#### SimonGeoghegan

##### Board Regular

- Joined
- Nov 5, 2013

- Messages
- 50

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: