SimonGeoghegan
Board Regular
- Joined
- Nov 5, 2013
- Messages
- 68
Hi All,
I have a range of risks with risk scores between 1-25
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.
I then have a formula in Cells I2:I6 which tells me what the score is:
And then a code in H2:H6 to give me the details:
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
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:
[FONT=Verdana]=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)))[/FONT]
I then have a formula in Cells I2:I6 which tells me what the score is:
Code:
[FONT=Verdana]=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)))[/FONT]
And then a code in H2:H6 to give me the details:
Code:
[FONT=Verdana]=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))))[/FONT]
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
Last edited: