Results 1 to 2 of 2

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

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,865
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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))))

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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