Formula to Find which category a value falls in

[Excel1991]

Hi all,

I have a spreadsheet that looks like the following
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Benchmark[/TD]
[TD]Benchmark[/TD]
[TD]Benchmark[/TD]
[TD]Benchmark[/TD]
[TD]Benchmark[/TD]
[/TR]
[TR]
[TD]Wages[/TD]
[TD][/TD]
[TD]10th[/TD]
[TD]25th[/TD]
[TD]50th[/TD]
[TD]75th[/TD]
[TD]90th[/TD]
[/TR]
[TR]
[TD]65,000[/TD]
[TD][/TD]
[TD]100,000[/TD]
[TD]200,000[/TD]
[TD]300,000[/TD]
[TD]400,000[/TD]
[TD]500,000[/TD]
[/TR]
[TR]
[TD]300,000[/TD]
[TD][/TD]
[TD]100,000[/TD]
[TD]250,000[/TD]
[TD]298,000[/TD]
[TD]350,000[/TD]
[TD]400,000[/TD]
[/TR]
[TR]
[TD]180,000[/TD]
[TD][/TD]
[TD]50,000[/TD]
[TD]60,000[/TD]
[TD]70,000[/TD]
[TD]120,000[/TD]
[TD]200,000[/TD]
[/TR]
</tbody>[/TABLE]

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:

Wages	10th	25th	50th	75th	90th

<tbody>
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[TH="align: center"]G[/TH]
[TH="align: center"]H[/TH]

[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]65,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]100,000[/TD]
[TD="align: right"]200,000[/TD]
[TD="align: right"]300,000[/TD]
[TD="align: right"]400,000[/TD]
[TD="align: right"]500,000[/TD]
[TD="align: right"]

Below 10th

<tbody>
</tbody>

[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]300,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]100,000[/TD]
[TD="align: right"]250,000[/TD]
[TD="align: right"]298,000[/TD]
[TD="align: right"]350,000[/TD]
[TD="align: right"]400,000[/TD]
[TD="align: right"]50th-75th[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]180,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50,000[/TD]
[TD="align: right"]60,000[/TD]
[TD="align: right"]70,000[/TD]
[TD="align: right"]120,000[/TD]
[TD="align: right"]200,000[/TD]
[TD="align: right"]75th-90th[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]300,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50,000[/TD]
[TD="align: right"]60,000[/TD]
[TD="align: right"]70,000[/TD]
[TD="align: right"]120,000[/TD]
[TD="align: right"]200,000[/TD]
[TD="align: right"]Above 90th[/TD]

</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