Need more than 7 formulas in one cell

  • Thread starter Thread starter Askew in help
  • Start date Start date
A

Askew in help

Here is the formula but it has more formulas than allowed in one cell. How do
I fix this error to keep the result in one field????

=IF(F20>=G21,10%,IF(F20>=G22,9%,IF(F20>=G23,8%,IF(F20>=G24,7%,IF(F20>=G25,6%,IF(F20>=G26,5%,IF(F20>=G27,4%,IF(F20>=G28,3%,IF(F20>=G29,"2%",IF(F20>=G30,"1%",IF(F20<=G30,"0%")))))))))))
 
If you "flip" your range values so that they're in ascending order rather
than descending order:

=IF(F20<G21,0,MATCH(F20,G21:G30)/100)

Format as Percentage
 
If you place your percentages in column "H" next to their corresponding
values in "G", then you can simply use a VLOOKUP. If you use "TRUE" for the
range_lookup, Excel will lookup the closest value that is greater than or
equal to the value that you are searching for. Just make sure that column G
is sorted from smallest to largest. For example, you could use
VLOOKUP(F20,$G:$H,2,TRUE) for the following data:

G H

0 0%
5 1%
10 2%
20 3%
30 4%
40 5%
50 6%
60 7%
75 8%
100 9%


There is no limit here, so you can add as many parameters as you like up to
the row limit of your version of Excel. You can create the reverse effect
(<=) by sorting from largest to smallest in column "G".


-BN
 
Back
Top