Functions

[WillieR]

Is there a way to nest more than 7 IF functions in Excel?? (or another way
to handle the data?) The formula I need uses 30 qualifiers.

 

[Dave R.]

You can possibly use a lookup statement, or a separate lookup table. It will
depend on what you are trying to do, but it can be done without all those
IFs. You can provide more detail.

 

[Bob Phillips]

No, but describe the problem and the data, there will always (?) be another
solution.

 

[WillieR]

Here's more detail. Any help would be greatly appreciated!

0 to $5.00 -4 points
5.01 to 10.00 -3 points
10.01 to 15.00 -2 points
15.01 to 20 -1 point
20.01 to 25 0 points
25.01 to 50 1 point
50.01 to 75 2 points
75.01 to 100 3 points
100.01 to 125 4 points
125.01 to 150 5 points
150.01 to 175 6 points
175.01 to 200 7 points
200.01 to 225 8 points
225.01 to 250 9 points
250.01 to 275 10 points
275.01 to 300 11 points
300.01 to 325 12 points
325.01 to 350 13 points
350.01 to 375 14 points
375.01 to 400 15 points
400.01 to 425 16 points
425.01 to 475 17 points
450.01 to 475 18 points
475.01 to 500 19 points
500.01 to 525 20 points
525.01 to 550 21 points
550.01 to 575 22 points
575.01 to 600 23 points
600.01 to 625 24 points
625.01 or greater 25 points

 

[Dave R.]

You can setup a lookup table, for example in A1 and B1 put

0 -4
then
5.01 -3
10.01 -2
.....
625.01 25

This will take up A1:B30

then use the formula

=VLOOKUP(C2,$A$1:$B$30,2)

where C2 is equal to the value you're looking up.

You could also do it within a vlookup function (long) like this
=VLOOKUP(C1,{0,-4;5.01,-3},2)

Also there is probably a way to do it taking into account the relationship
between your numbers, and a couple IF statements to cover the jump from 5 to
25..

 

[Dana DeLouis]

Don't know if this is any good, but just an idea...

=IF(A1<=25,FLOOR(MAX(-4,0.2*A1-5),-1),MIN(CEILING(0.04*A1-1,1),25))