creating a summary list depending on scores

[narrowdave]

Hi, am hoping someone can point me in the right direction...

I have created an excel based audit tool for hospitals. It takes scores from staff and managers in response to around 30 questions. These are combined and triangulated to produce a lovely dashboard / performance summary... All good so far...

So here's the thing... To the dashboard I am wondering about adding a box / table that shows the top 5 recommendations for action... So basically I'd create a list of 30 or so possible actions to improve performance - then on the basis of those which if implemented will make the biggest difference to the overall performance scores, on my dashboard I want to be able to show a list of the top 5... Obviously this needs to be capable of live updating if and when scores change...

I can't for the life of me think how to begin. I've already got hidden sheets with scores and corresponding actions but can't think how to pull through the top 5...

It may be post-Xmas brain fog but any thoughts on how to do this?? I'm thinking some form of table...

Thanks

Dave

 

[oldbrewer]

if you have a list of all actions, who decides which will make the biggest difference - or do you apply a savings figure, if so take the top 5 savings figures using the large function

 

[Logit]

.
Are you over thinking this ?

Finalize your scores for all who were surveyed. Sort the list of scores HIGH to LOW.

Copy paste top 5.

Or is there more to what is going on in your workbook ?

 

[narrowdave]

So the workbook has 30 or so questions for staff and managers.. Each set tap different, though related concepts... Questions relate 17 different standards - therefore some answers relate to more than one standard. Mean scores are calculated for each standard and staff and manager responses have a moderating influence on each other... There's loads of hidden algorithms running in the background.

The performance summary includes lots of visuals - overall compliance, projected potential for improvement over the next 12 months etc... These all work fine and have been piloted...

There's then an action planning tool that managers and staff collaborate on - although I don't want to be prescriptive and people need to create their own improvement plans, I want to offer some tangible suggestions... So as a result of the 30ish questions, I have around 30 suggestions for improvement - I can come up with a score based on ratings for each... Based on the likely impact of each potential action, I can calculate an 'improvement quotient'. I will do all of this on a hidden sheet... What I want to do is pull through the top 5 actions on the basis that they have the highest 'improvement quotient'. This will appear on the performance summary...

Once finished the workbook will be disseminated freely to hospitals and so it will be heavily locked down and needs to be as self complained as possible... It will be used by doctors and nurses, rather than tech savvy people...

I'm just not sure how to pull through the top 5 of 30 potential actions on the basis of improvement quotients, and display them in a table on a separate worksheet...

Hope that makes some sort of sense!! Thanks...

 

[oldbrewer]

if you have a list of 30 potential actions you need to score them for cost, difficulty, time to implement, and potential benefit, then take the top 5. I was a business improvement for a very large organisation in London, and I think you need to let the people at the coal face identify and implement improvements. IT should be last, not first................

 

[narrowdave]

Thanks for your suggestions. The intention is absolutely not to be prescriptive but rather to have a 'top five' 'things for consideration' (with this being heavily evidence based)... really it's just as a starting point for local discussions about improvement planning...

The tool is intended to assist hospitals make improvements to ensure compliance with statutory obligations and to take action on known causes of preventable deaths. The standards, questions and potential actions have been developed with large groups of patients, healthcare practitioners and academics. I am a nurse (still in practice) and am involved in a range of national improvement initiatives - I'm reasonably good with Excel, though no expert!! The aim is to move beyond external scrutiny and regulation - rather to respond to requests from healthcare providers and develop a free tool that guides their planning...

The IT won't be driving things, I've had lots of advice from research colleagues who specialise in sociotechnical issues and concepts and recognise that outcome focused solutions need to be rooted in local context and mechanisms... The suggestions for improvement arising from the tool are outcome focused and have been developed by hands-on healthcare workers... Translating these into actions will require people locally to consider local context etc... and the tool will have inbuilt prompts to this effect...

 

[oldbrewer]

Thanks for that. Maybe you have a list of actions where scores are being modified all the time. If so, you can use the large function, eg large($a$1:$a$10,3) will return the third largest score. If you want to list the top 3 of several types of improvements it is more complicated but can be done. Can you make some pretend data up to show us the data you are working with. Finally are you building in improvement actions from local audits, management tours etc.

 

[narrowdave]

Have hopefully inserted partial screenshot below...

https://photos.app.goo.gl/X8eaS2dvNSUVkGLo8

Each standard has a 'raw score' - this is a combination of staff and managers ratings... Each of the potential recommendations has a weighted potency (based on the fact that some recommendation affect other standards). These are combined to give rise to a weighted score... The lower the score, the greater the potency of responding to the recommendation.

So what I want to do is pick out the 'lowest' five scores and then rather than displaying the score itself, just show the text recommendation...

Hopefully that makes some sort of sense...

Thanks eve so much for your help.

 

[oldbrewer]

[TABLE="width: 832"]
<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]row1[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]issue1[/TD]
[TD="align: right"]6.5[/TD]
[TD][/TD]
[TD]row2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]issue2[/TD]
[TD="align: right"]5.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]issue3[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]issue4[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]issue5[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]issue6[/TD]
[TD="align: right"]4.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]issue7[/TD]
[TD="align: right"]5.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]the three issues with the lowest scores are[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]issue8[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]issue9[/TD]
[TD="align: right"]7.2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]issue6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]issue10[/TD]
[TD="align: right"]9.9[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]issue7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]issue2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]formula giving issue6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]=OFFSET($B$1,MATCH(SMALL($B$2:$B$11,E10),$B$2:$B$11,0),-1)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[narrowdave]

Thanks ever so much with a bit of tweaking works a treat... had forgotten all about the offset function!!

Cheers