First, the formula will be easier to create if you arrange your data this way:
1200 1500 1700 1700 1998 2700
20th %tile 21st %tile 22nd %tile 23th %tile 24th %tile 25th
I tested this with those values in cells D1:I2
A1: 2500
B1: =HLOOKUP(A1,$D$1:$I$2,2,1)
Is that something you can work with?
--
Regards,
Ron
"Chang" wrote:
Hi,
I need to look up values from a range of numbers, like between. Can somebody
help me with it?
For example one worksheet Cell A1 I have number 2500. In another worksheet
I have 2 rows data like the following:
20th %tile 21st %tile 22nd %tile 23th %tile 24th %tile 25th
%tile ...........90th
1200 1500 1700 1700 1998 2700
...........8885
In this case, the formula cell should return me the 24th %tile sine 2500 is
between 1998 and 2700. Keep in mind some cells have the same number like 22nd
and 23th are the same and since I have data from 20th all the way to 90th, I
can NOT write if function for 70 some times.
I really appreciate anybody can help me!
--
Chang
|