+ Reply to Thread
Results 1 to 3 of 3

adding a IF function to an array formula? is this possible?

  1. #1
    Registered User
    Join Date
    09-05-2016
    Location
    Vancouver, Canada
    MS-Off Ver
    365
    Posts
    63

    adding a IF function to an array formula? is this possible?

    So what I need to do is create a formula that will help to solve an issue where I need to add I think an IF function to a array formula that I have that is looking at one value and determining if it is within a range and then giving another cell a number based on that range. The array formula that I have now works great but it is based on a max number of 32.
    what i need to have now is the formula needs to be adjusted when that number is changed to 16 or 8. I know I can just adjust the formula to do that but I need it to do it automatically by changing the one cell.
    I have included a doc that has what I am thinking. so A column has the list of numbers from 1-35 (ranking) the formula row B column right now has the array formula in it. what I need is when you change the yellow box to either 32 , 16 , 8 it will adjust the formula automatically to show the below info

    if yellow box says 32 *** the formula already does this but not linked to the yellow box***
    1-8 will be 4
    9-16 it will be 2
    17-32 it will be 1
    less then 16 will be 0

    if yellow box says 16
    1-8 it will be 3
    9-16 it will be 1
    less then 16 will be 0


    If the yellow box is 8
    1-8 it will be 2

    I am thinking there needs to be a IF function in there that will adjust the formula based on what is in the yellow box.


    thanks
    Attached Files Attached Files

  2. #2
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: adding a IF function to an array formula? is this possible?

    Thanks cnak. Paste this in B5 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Each Boolean clause (x>y) yields 1 if TRUE, 0 if FALSE. Addition and multiplication do the rest!

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Last edited by leelnich; 05-17-2017 at 10:10 AM.

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2504
    Posts
    13,744

    Re: adding a IF function to an array formula? is this possible?

    If I have interpreted correctly try this in B5 and fill down.

    =LOOKUP(A5,{1,9,17,33},CHOOSE(MATCH($B$2,{32,16,8},-1),{4,2,1,0},{3,1,0,0},{2,1,0,0}))
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Adding another condition into array formula
    By simmo86 in forum Excel General
    Replies: 3
    Last Post: 01-19-2017, 09:59 AM
  2. [SOLVED] Adding another If Statement to an array formula
    By SJohnsonbaugh in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-16-2016, 12:30 PM
  3. Replies: 3
    Last Post: 04-02-2016, 08:16 PM
  4. Adding criteria to an array formula
    By jonagpa in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 01-09-2016, 11:28 PM
  5. Adding IF to an Array formula
    By Motox in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-18-2015, 08:51 AM
  6. Adding an extra layer to this array formula
    By liquidmettle in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-04-2015, 04:46 PM
  7. adding Indirect formula to my current sum if array formula
    By Eastbay2 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-13-2013, 09:41 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1