Nested IF functions beyond eight items?

[Bob Phillips]

small change for negatives Kostis

=CEILING(MAX(A12,0),5)/2


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

I am trying to write a formula using the IF funciton. The formula itself is
fine. My problem is, though, I need more than just seven conditions. I
actually need 12

The formula currently reads like this:

=IF(A12<=0,0, IF(A12<=5, 2.5, IF(A12<=10,5, IF(A12<=15, 7.5, IF(A12<=20,10,
IF(A12<=25, 12.5, IF(A12<=30, 15, IF(A12<=35, 17.5))))))))

As can be seen, this formula criteria goes in increments of 5. I need to be
able to expland this formula to include amounts for <=40, <=45, <=50, <=55,
<=60, which would mean I would need 5 more IF statements in that string.
However, it will not let me go beyond eight of them. Does anyone have an
idea of how I can get around this and make my formula work?

Thanks!

 

[vezerid]

Sherry,
in your example, the value returned by each IF is half the upper limit.
Since the upper limit of each interval increases by 5, and if this
pattern continues, then you can replace your formula with:

=CEILING(A12,5)/2

HTH
Kostis Vezerides

 

[vezerid]

Hi Bob,

I guess I have been rusty too long. Actually nice idea for an
assignment <wide evil grin>.

Kostis