Nested if 3 conditions

[Dan88]

Hello, can you please help point me in the right direction on this formula.

Basically it has 3 conditions depending on day count.

If <60 it should return “80%”
If >60 but under 180 it should return 75%
And if over 180 it should return 65%


I am trying this but it wont work.:

=IF(A3<=60,"80%",IF(AND(A3>=60,A3<=180,"75%"),IF(A3>=180,"65%")))

Any help or pointers would be greatly appreciated. Thanks!

 

[Special-K99]

=LOOKUP(A3,{0,60,180},{0.8,0.75,0.65})

format the result as a percentage

 

[Gerald Higgins]

OR

=if(A3<=60,0.8,if(A3<=180,0.75,0.65))

And as Special-K99 said, format the result as percentage.

 

[Dan88]

Special K and Gerald H, you guys are legendary! thanks so much for the quick response. this is exactly what i needed!!
Many many thanks

 

[Gerald Higgins]

And just for fun, here's another approach.

Again, format the result as percentage.

=0.65+(A3< =180)*0.1+(A3< =60)*0.05

 

[shknbk2]

The other two replies are just fine and present alternative formulas. However, I thought I'd just point out a correction to your original formula so you could troubleshoot where it went wrong.

Your formula:
=IF(A3<=60,"80%",IF(AND(A3>=60,A3<=180,"75%"),IF(A3>=180,"65%")))
Corrected:
=IF(A3<=60,"80%",IF(AND(A3>=60,A3<=180),"75%",IF(A3>=180,"65%")))

The red parenthesis was in the wrong place. It needs to go after the 180 in order to close out the AND formula.

 

[Dan88]

Thank you ALL for your valuable inputs.

I would like to expand on this Nestedif statement if at all possible.


Can the statement look up H3 and if “RL” AND if AC3 is >50%, then apply LOOKUP(AE3,{0,60,180},{0.8,0.75,0.65}).

Thanks!!!

 

[Dan88]

Thank you ALL for your valuable inputs.

I would like to expand on this Nestedif statement if at all possible.


Can the statement look up H3 and if “RL” AND if AC3 is >50%, then apply LOOKUP(AE3,{0,60,180},{0.8,0.75,0.65}).

 

[Fluff]

How about
=IF(AND(H3="RL",AC3>50%),LOOKUP(AE3,{0,60,180},{0.8,0.75,0.65}),"else whatever")

 

[Dan88]

Fluff, my bad i screwed up on the ask. it should really be like this:

Can the statement look up H3 and if “RL” AND if AC3 is >50%, then apply LOOKUP(AE3,{0,60,180},{0.8,0.75,0.65} BUT if AC3 < 50% then it should be this LOOKUP(AE3,{0,60,180},{0.55,0.65,0.75}


Thank yuou!!