Matrix visual and compare values

[boehnc]

Hello all,

I have the below in a Matrix visual. I'd like to add another column in the visual that would compare the "Score" to the %ile columns to give me the correct value.
For example, Team A's score is 0.75. This is more than 0.65 but less than 0.76 so it should be in the 25th %ile. Team B is in the 10th %ile and so forth. Thanks for any help.



Team Score 10th %ile 25th %ile 50th %ile 75th %ile 90th %ile
A 0.75 0.5 0.65 0.76 0.95 0.99
B 0.52 0.45 0.55 0.65 0.85 0.93
C 0.56 0.42 0.57 0.69 0.75 0.84
D 0.91 0.65 0.79 0.89 0.93 0.99

 

[Legacy 98055]

There's probably a more elegant solution, but I had fun.

	A	B	C	D	E	F	G	H
	Team Score	10th %ile	25th %ile	50th %ile	75th %ile	90th %ile	Result
A	0.75	0.5	0.65	0.76	0.95	0.99	25th %ile
B	0.52	0.45	0.55	0.65	0.85	0.93	10th %ile
C	0.56	0.42	0.57	0.69	0.75	0.84	10th %ile
D	0.91	0.65	0.79	0.89	0.93	0.99	50th %ile

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

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

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

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

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=IF(AND(B2>=C2,B2<D2),$C$1,IF(AND(B2>=D2,B2<E2),$D$1,IF(AND(B2>=E2,B2<F2),$E$1,IF(AND(B2>=F2,B2<G2),$F$1,G2))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=IF(AND(B3>=C3,B3<D3),$C$1,IF(AND(B3>=D3,B3<E3),$D$1,IF(AND(B3>=E3,B3<F3),$E$1,IF(AND(B3>=F3,B3<G3),$F$1,G3))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4[/TH]
[TD="align: left"]=IF(AND(B4>=C4,B4<D4),$C$1,IF(AND(B4>=D4,B4<E4),$D$1,IF(AND(B4>=E4,B4<F4),$E$1,IF(AND(B4>=F4,B4<G4),$F$1,G4))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H5[/TH]
[TD="align: left"]=IF(AND(B5>=C5,B5<D5),$C$1,IF(AND(B5>=D5,B5<E5),$D$1,IF(AND(B5>=E5,B5<F5),$E$1,IF(AND(B5>=F5,B5<G5),$F$1,G5))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]