Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Multiple IFs in a function...

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!

Posted on Aug 27, 2013 11:08 AM

Reply
6 replies

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

Multiple IFs in a function...

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.