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

[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 ?.

[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))))

[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.

[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

Post by 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 ?.
--
Daywalker
------------------------------------------------------------------------
http://www.excelforum.com/member.php?action=getinfo&userid=32806
View this thread: http://www.excelforum.com/showthread.php?threadid=526228

[Sandy Mann]

One way:

=MIN(MAX(INT((A54-51)/10),0),4)*5
--
HTH

Sandy

Post by 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 ?.
--
Daywalker
------------------------------------------------------------------------
http://www.excelforum.com/member.php?action=getinfo&userid=32806
View this thread: http://www.excelforum.com/showthread.php?threadid=526228

[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.

[Nozza]

Have you thought of using a vlookup?

Noz