BIG IF statement problem

[Greg Parker]

I can not get this if statement to work any suggestions.

=IF(M11>25%,"1%",IF(M11>33%,"4%",IF(M11>40%,"4.5%",IF(M11>45%,"5%",IF(M11>55%,"7%",IF(M11>60%,"7%",IF(M11>65%,"7.5%",IF(M11>70%,"8%",IF(M11>75%,"8.5%",IF(M11>80%,"9%",IF(M11>85%,"10%",IF(M11>90%,"11%",IF(M11>95%,"13%",IF(M11>100%,"15%",IF(M11>110%,"16%",IF(M11>120%,"18%",IF(M11>130%,"19%",IF(M11>140%,"20%",IF(M11>150%,"23%")))))))))))))))))))*K11

 

[Gord Dibben]

Greg

Since Excel allows only 7 "IF"'s in a formula, I don't doubt it doesn't work.

You may be much better off using a VLOOKUP table with your values in one
column and your rates in an adjacent column.

Format as Percentage

Then in M11 enter this formula =VLOOKUP(M10,table,2,FALSE)*K11

Format M10 as Percentage.

Where table is the range of the two columns with values and rates.

Enter a value of 25 in M10 and return 1% * K11 in M11


Gord Dibben Excel MVP

 

[Pete]

You could set up a small lookup table along the lines of:

0% '0%
25% '1%
33% '4%
40% '4.5%

etc. The apostrophe in front of the second percentage will force it to
text, which I assume is what you want. You will have 20 entries (rows)
in the table, let's say in cells P1 to Q20.

Then you can replace the multiple IFs with this formula:

=VLOOKUP(M11,$P$1:$Q$20,2,TRUE) * K11.

I assume that anything less than 25% should be 0%.

Pete

 

[Pete]

Sorry, you won't be able to multiply by K11 if you return text values -
omit the apostrophes in the table.

Pete

 

[Guest]

1. You should use VLOOKUP
2. The logic is bad. Even if you could nest the IFs, If M11 > 25% than 1%
is returned and none of the other tests are performed.