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

 

[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

 

[Sandy Mann]

One way:

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

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

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

 

[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