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%")))))))))))
T. Valko - 28 Mar 2008 07:08 GMT
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

Signature
Biff
Microsoft Excel MVP
> 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%")))))))))))
BOONER - 28 Mar 2008 07:50 GMT
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
> 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%")))))))))))