Thread: Matrix visual and compare values Thanks: 0 Likes: 0

1. Matrix visual and compare values

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  Reply With Quote

2. Re: Matrix visual and compare values

There's probably a more elegant solution, but I had fun. A B C D E F G H
1 Team Score 10th %ile 25th %ile 50th %ile 75th %ile 90th %ile Result
2 A 0.75 0.5 0.65 0.76 0.95 0.99 25th %ile
3 B 0.52 0.45 0.55 0.65 0.85 0.93 10th %ile
4 C 0.56 0.42 0.57 0.69 0.75 0.84 10th %ile
5 D 0.91 0.65 0.79 0.89 0.93 0.99 50th %ile
Sheet1

Worksheet Formulas
Cell Formula
H2 =IF(AND(B2>=C2,B2),\$C\$1,IF(AND(B2>=D2,B2),\$D\$1,IF(AND(B2>=E2,B2),\$E\$1,IF(AND(B2>=F2,B2),\$F\$1,G2))))
H3 =IF(AND(B3>=C3,B3),\$C\$1,IF(AND(B3>=D3,B3),\$D\$1,IF(AND(B3>=E3,B3),\$E\$1,IF(AND(B3>=F3,B3),\$F\$1,G3))))
H4 =IF(AND(B4>=C4,B4),\$C\$1,IF(AND(B4>=D4,B4),\$D\$1,IF(AND(B4>=E4,B4),\$E\$1,IF(AND(B4>=F4,B4),\$F\$1,G4))))
H5 =IF(AND(B5>=C5,B5),\$C\$1,IF(AND(B5>=D5,B5),\$D\$1,IF(AND(B5>=E5,B5),\$E\$1,IF(AND(B5>=F5,B5),\$F\$1,G5))))  Reply With Quote

User Tag List

Tags for this Thread

%ile, 25th, score, team, visual  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•