Displaying Percentiles

[Michael L]

Hello,
I need to display percentiles based on vlues I input, but I am limited
to 7 nested IF statements. Could somebody show me a way to do this
which will allow me to display the percentiles from 99% to 1%? Here is
what I have right now:

=IF(F3<6.68,"99"%,IF(F3<7.09,"98"%,IF(F3<7.72,"97"%,IF(F3<7.77,"96"%,IF(F3<8.1,"95"%,IF(F3<8.44,"94"%,IF(F3<8.96,"93"%,IF(F3<9.14,J3))))))))

thanks,
Mike

 

[Guest]

You can do building a two column list and using VLOOKUP. Following your
formula, you can create a list like this:
[Col A] [Col B]
0 99%
6.68 98%
7.09 97%
7.72 96%
7.77 95%
8.1 94%
....

And then, with the value you want to look at in cell C2, for example, you
can use this formula:
=VLOOKUP(C2,$A2:$B100,2)
The formula will look for the number closer to C2 in column A that is lower
than C2, and return the value on the column B.

Hope this helps,
Miguel.