Probably a simple "IF" calculation, but ???

D

Daywalker

Looking for some help. I am trying to set up an "IF" statement to look
at a value and if it is => then enters another value in another cell.
This is what I have done
=IF(D54>=61,5,IF(D54>=71,10,IF(D54>=81,15,IF(D54>=91,20,0)))).

Now, when I enter a value under 61, then the cell does show "0". But
above the number 61, it always is showing "5", when I want it to show
the following.

=> 61, should show 5
=> 71, should show 10
=> 81, should show 15 and so on.

Must be something stupid I am missing here ?. Anyone help me out ?.
 
T

Trevor Shuttleworth

=IF(D54>=91,20,IF(D54>=81,15,IF(D54>=71,10,IF(D54>=61,5,0))))

You need to test for the biggest number first and work your way down.
Anything that is bigger than 91 will be bigger than 61 ...

Regards

Trevor
 
M

Mark Lincoln

You just need to do these in reverse order:

=IF(D54>=91,20,IF(D54>=81,15,IF(D54>=71,10,IF(D54>=61,5,0))))
 
M

Mark Lincoln

What I meant to say is that you need to do your IFs in reverse order.
Test for the largest value first and the smallest value last, as in the
formula I posted. In your formula, anything 61 or over made your first
IF test true, so your value in D54 would then always be 5.
 
D

Daywalker

Yip, it was something stupid I was missing. Must be a Friday night
thing. Just reversed the numbers from highest to lowest and works
brill.

Thanks for your help.

John.
 
Top