i need a formula to pick up data

[junkie_7]

i have a whole bunch of salaries to be matched against their specific salary range to determine which percentile group they are in (i.e. 25th percentile, 50th percentile and 75th percentile).

i was using 'VLOOKUP' but when the values are in between, they are unable to match to the percentile group.

i.e. the salary range will be:
25th percentile: $2100
50th percentile: $2750
75th percentile: $3400

lets say person A's salary is $2750, it will pick up 50th percentile but when it comes to person B, whose salary is $3100, it gives me #VALUE!. so is there another way/ formula for me to do this?

THANKS!

 

[Trebor76]

Hi there,

Try this (just change the cell reference (from A2) to suit):

=IF(ISNUMBER(A2)=TRUE,IF(A2<=2100,"25th percentile",IF(AND(A2>2100,A2<=2750),"50th percentile","75th perentile")),0)

HTH

Robert

 

[junkie_7]

Thanks! it works!

 

[Trebor76]

Glad to have helped