Skip navigation

Multiple IFs in a function...

294 Views 6 Replies Latest reply: Aug 29, 2013 11:12 AM by Barry RSS
Equius Level 1 Level 1 (0 points)
Currently Being Moderated
Aug 27, 2013 11:08 AM

I'm having difficulties with the IF function in Nunbers. Here's what I want to do:

 

In cell C3, I would like to enter a percentage and have cell B6 change.

 

For example, if C3 is <=5%, I want B6 to display 30%. If C3 is >5%<=10%, I would like B6 to display 40%. If C3 is >10%<=15%, I want B6 to display 50%. And if C3 is >=15%, I want B6 to display 60%. Can someone help me with this? Thank you!

  • Wayne Contello Level 6 Level 6 (12,585 points)
    Currently Being Moderated
    Aug 27, 2013 12:23 PM (in response to Equius)

    You can use an extra table and a vlookup() function like this:

     

    Screen Shot 2013-08-27 at 2.21.39 PM.png

     

    B6=VLOOKUP(C3, Mapping :: A:B, 2)

     

    I named the table on the right "Mapping"

     

    main caveat is...

     

    vloopup does a strictly less than test so your bins are actually:

     

    X <5  -> 30

    5 >= X < 10  -> 40

    10 >= X < 15  -> 50

    15 >= X < 1000  -> 60

  • Wayne Contello Level 6 Level 6 (12,585 points)
    Currently Being Moderated
    Aug 27, 2013 1:17 PM (in response to Equius)

    OK.  now add the following to your formula:

     

    X <5  -> 30

    5 >= X < 8  -> 35

    8 >= X < 10  -> 40

    10 >= X < 12.5  -> 45

    12.5 >= X < 15  -> 50

    15 >= X < 1000  -> 60

     

    Screen Shot 2013-08-27 at 3.16.01 PM.png

  • Wayne Contello Level 6 Level 6 (12,585 points)
    Currently Being Moderated
    Aug 27, 2013 1:18 PM (in response to Wayne Contello)

    How about a few more:

    Screen Shot 2013-08-27 at 3.18.18 PM.png

     

    Didn't have to change the formula at all

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Aug 29, 2013 11:12 AM (in response to Equius)

    Hi Equius,

     

    Agreed that the VLOOKUP soution suggested by Wayne is the better one in this situation.

     

    As an aside, though, your equation using nested IFs could be simplified as well, as there's no need for the use of AND.

    The second IF, where the conditions are C3 > 5% AND C3 <=10% will be reached only if C3 > 5%. Since that will always be TRUE at this point, there's no need to retest it.

     

    Original: =IF(C3<=5%,30%,IF(AND(C3>5%,C3<=10%),40%,IF(AND(C3>10%,C3<=15%),50%,60%)))

     

    Revised: =IF(C3<=5%,30%,IF(C3<=10%,40%,IF(AND(C3<=15%),50%,60%)))

     

    To exactly match the results for the VLOOKUP solution, this further revision is needed:

     

    Revised2: =IF(C3<5%,30%,IF(C3<10%,40%,IF(AND(C3<15%),50%,60%)))

     

    Wayne's demonstration of the increasing complexity of the nested IF formula (and consequent increasing probability of making an error in editing the formula) still applies, though.

     

    Regards,

    Barry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.