Thread: Formula to find highest unique values in a range Thanks: 0 Likes: 0

1. Formula to find highest unique values in a range

Hi All,

I have a range of risks with risk scores between 1-25
("F2:F2460"), and
details about each of these risks within the range ("E2:E2460")

I need to provide a list which will return the top 'X' amount of risks depending on what the risk scores are. I need to show a minimum of 5, however if there are further risks which score the same as the 5th highest risk, they would also show, so I could have maybe 12,13,14 etc. that I need to display.

I have a hospital name in Cell H1.

I have a formula in Cell I1 which identifies how many instances of the highest risk score there are, for the hospital in H1.

Code:
=SUM(IF(Risks!\$A2:\$A2460=\$H\$1,IF(Risks!\$F\$2:\$F\$2460>=LARGE(IF(Risks!\$A\$2:\$A\$2460=\$H\$1,Risks!\$F\$2:\$F\$2460),MIN(5,COUNT(IF(Risks!\$A\$2:\$A\$2460=\$H\$1,Risks!\$F\$2:\$F\$2460)))),1)))
I then have a formula in Cells I2:I6 which tells me what the score is:

Code:
=IF(ROWS(\$I\$2:I2)>\$I\$1,"",LARGE(IF(Risks!\$A\$2:\$A\$2460=\$H\$1,Risks!\$F\$2:\$F\$2460),ROWS(\$I\$2:I2)))
And then a code in H2:H6 to give me the details:

Code:
=IF(\$I2="","",INDEX(Risks!\$L\$2:\$L\$2460,SMALL(IF(Risks!\$A\$2:\$A\$2460=\$H\$1,IF(Risks!\$F\$2:\$F\$2460=\$I2,ROW(Risks!\$A\$2:\$A\$2460)-ROW(\$A\$2)+1)),COUNTIFS(\$I\$2:I2,I2))))
Is anybody able to point me in the right direction at all as to whether this can be achieved? I'm assuming it can, I just cannot think how to go about it!

Thanks in advance of any help!

Simon  Reply With Quote

2. Re: Formula to find highest unique values in a range

Simon,

I think I'm onboard and it looks like you almost have it but shouldn't that final H2:H6 (which should probably be H2:H99 to catch duplicates of the last risk score)
INDEX(Risks!\$L\$2:\$L\$2460

...actually be...
INDEX(Risks!\$E\$2:\$E\$2460

A B C D E F G H I
1 Hospital Risk Risk Score St Elsewhere 8
2 St Elsewhere Poisoning from Hospital meal 18 Risk 8 20
3 St Elsewhere Surgeon can't tell Left from Right 6 Poisoning from Hospital meal 18
4 St Elsewhere Stub toe on bed 11 Hospital Bill gives heart attack 18
5 St Elsewhere Hospital Bill gives heart attack 18 Risk 6 17
6 St Elsewhere Risk 5 5 Risk 7 17
7 St Elsewhere Risk 6 17 Risk 24 17
8 St Elsewhere Risk 7 17 Risk 26 17
9 St Elsewhere Risk 8 20 Risk 27 17
10 St Elsewhere Risk 9 10
11 St Elsewhere Risk 10 6
Risks

Array Formulas
Cell Formula
I1 {=SUM(IF(Risks!\$A2:\$A2460=\$H\$1,IF(Risks!\$F\$2:\$F\$2460>=LARGE(IF(Risks!\$A\$2:\$A\$2460=\$H\$1,Risks!\$F\$2:\$F\$2460),MIN(5,COUNT(IF(Risks!\$A\$2:\$A\$2460=\$H\$1,Risks!\$F\$2:\$F\$2460)))),1)))}
H2 {=IF(\$I2="","",INDEX(Risks!\$E\$2:\$E\$2460,SMALL(IF(Risks!\$A\$2:\$A\$2460=\$H\$1,IF(Risks!\$F\$2:\$F\$2460=\$I2,ROW(Risks!\$A\$2:\$A\$2460)-ROW(\$A\$2)+1)),COUNTIFS(\$I\$2:I2,I2))))}
I11 {=IF(ROWS(\$I\$2:I11)>\$I\$1,"",LARGE(IF(Risks!\$A\$2:\$A\$2460=\$H\$1,Risks!\$F\$2:\$F\$2460),ROWS(\$I\$2:I11)))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

Personally I'd use AGGREGATE to avoid the array formulae but you're so close it's probably not worth the surgery.

Regards,  Reply With Quote

3. Re: Formula to find highest unique values in a range

Thanks for this Toadstool, works perfectly now! Appreciate your help and nice to know that I wasn't too far away from what I wanted to achieve!  Reply With Quote

4. Re: Formula to find highest unique values in a range

You're welcome!

...and I trust my test data for column E risk descriptions is nowhere near the actual data   Reply With Quote

User Tag List

Tags for this Thread

formula, highest, risk, risks, score  Posting Permissions

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