Formula to Find which category a value falls in

[Excel1991]

Hi all,

I have a spreadsheet that looks like the following

		Benchmark	Benchmark	Benchmark	Benchmark	Benchmark
Wages		10th	25th	50th	75th	90th
65,000		100,000	200,000	300,000	400,000	500,000
300,000		100,000	250,000	298,000	350,000	400,000
180,000		50,000	60,000	70,000	120,000	200,000

<tbody>
</tbody>

I was wondering if there was a formula i could put in a new column that would let me know which Benchmark percentile the value in column A falls within. Also, is there a way to perform an "IFS" statement to say "if the wages in column in A are less than then the 10th% then say "less than 10%", if the value in column a is between 10th % and 25th% then say "10th-25th"" and so on?

Thank you!

 

[Excel1991]

For more explanation, the $65,000 in wages falls below the benchmark of the 10th %, so I am trying to get an if statement to say that it falls below the 10th. If i replaced the $65,000 with 350,000, I want it to say that it falls between the 50th and 75th. Is this possible?

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H
1	Wages		10th	25th	50th	75th	90th
2	65,000		100,000	200,000	300,000	400,000	500,000	Below 10th
3	300,000		100,000	250,000	298,000	350,000	400,000	50th
4	180,000		50,000	60,000	70,000	120,000	200,000	75th
Sheet2

Cell Formulas
Range	Formula
H2	=IFERROR(LOOKUP(A2,C2:G2,$C$1:$G$1),"Below 10th")

 

[Excel1991]

Is there a way that I can make it say the range that it falls? For instance:

	A	B	C	D	E	F	G	H
1	Wages		10th	25th	50th	75th	90th
2	65,000		100,000	200,000	300,000	400,000	500,000	Below 10th <tbody> </tbody>
3	300,000		100,000	250,000	298,000	350,000	400,000	50th-75th
4	180,000		50,000	60,000	70,000	120,000	200,000	75th-90th
5	300,000		50,000	60,000	70,000	120,000	200,000	Above 90th

<tbody>
</tbody>

 

[Fluff]

How about
=IFNA(CHOOSE(MATCH(A2,C2:G2,1),"10th-25th","25th-50th","50th-75th","75th-90th","Above 90"),"Below 10")

 

[Excel1991]

This works perfectly! Thank you so much

 

[Fluff]

My pleasure & thanks for the feedback

 

[Excel1991]

Fluff,

I have one follow up question if you have the time. Would there be a way to indicate which exact percentile the value in column a falls at? The ranges i provided are benchmarks. So for instance, I know the $65,000 in call A2 falls below the 10th percentile, but is there a way to see what exact percentile it falls at (ex. 6th percentile)?

I dont think that I could use the percentile rank functions because that formula would think that my 90th percentile is actually the 100th percentile. If is use the percentile rank I would use the ranges above as my array. For instance, using my example above, the value in a3 ($180,000) falls just below the 90th. however, if i use the percentile rank function, it would say it falls 95th percentile or something because it thinks that $200,000 is actually the 100th percentile.

I hope what I am saying makes sense, it is difficult to explain.

 

[Fluff]

Makes absolutely no sense to me, but that's probably because I know nothing of percentiles.

As this is a different question, you need to start a new thread.
Thanks