Hello!
I am looking for help with an excel document I have which has a list of names (currently six but likely to increase to more than twenty) and how many points they have for a particular task which results in a percentage of the total possible points they could have had.
I would like a formula which would look for the top 3 highest scorers and return their names in a cell.
I have created a quick excel sheet which you will hopefully see what I would like to achieve.
So based on the current data, I would like to auto populate cell M19 with the names as follows:
1. John, Peter 95.1%
M20 will be:
2. Karen 85.4%
and M21 will be:
3. Julie 82.9%
Is there a way I can achieve this? It will often be multiple people with the same score so I need to ability to return more than one name in the cells where this occurs.
I hope this makes sense.
Thank you as always for your help!
I am looking for help with an excel document I have which has a list of names (currently six but likely to increase to more than twenty) and how many points they have for a particular task which results in a percentage of the total possible points they could have had.
I would like a formula which would look for the top 3 highest scorers and return their names in a cell.
I have created a quick excel sheet which you will hopefully see what I would like to achieve.
| Example Tasks Region.xlsx | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
| 1 | Number | Name | Saturday | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Totals | ||||||||||||
| 2 | A | B | A | B | A | B | A | B | A | B | A | B | A | B | A | B | A + B | % | ||||
| 3 | Possible Points Each | 0 | 3 | 0 | 0 | 6 | 4 | 5 | 1 | 5 | 3 | 4 | 3 | 5 | 2 | 25 | 16 | 41 | ||||
| 4 | Possible Points Team | 0 | 18 | 0 | 0 | 36 | 24 | 30 | 6 | 30 | 18 | 24 | 18 | 30 | 12 | 150 | 96 | 246 | ||||
| 5 | 4149 | John | 3 | 6 | 4 | 5 | 1 | 5 | 3 | 4 | 3 | 5 | 0 | 25 | 14 | 39 | 95.1% | |||||
| 6 | 4178 | David | 2 | 4 | 4 | 5 | 0 | 5 | 3 | 4 | 1 | 5 | 0 | 23 | 10 | 33 | 80.5% | |||||
| 7 | 4279 | Peter | 3 | 6 | 4 | 5 | 1 | 5 | 2 | 4 | 3 | 5 | 1 | 25 | 14 | 39 | 95.1% | |||||
| 8 | 4419 | Karen | 3 | 5 | 4 | 5 | 1 | 3 | 3 | 4 | 2 | 5 | 0 | 22 | 13 | 35 | 85.4% | |||||
| 9 | 4638 | Emma | 3 | 6 | 3 | 5 | 1 | 3 | 3 | 4 | 3 | 0 | 0 | 18 | 13 | 31 | 75.6% | |||||
| 10 | 4639 | Julie | 0 | 5 | 4 | 5 | 1 | 4 | 3 | 4 | 3 | 5 | 0 | 23 | 11 | 34 | 82.9% | |||||
| 11 | Total | 0 | 14 | 0 | 0 | 32 | 23 | 30 | 5 | 25 | 17 | 24 | 15 | 25 | 1 | 136 | 75 | 211 | 85.8% | |||
| 12 | ||||||||||||||||||||||
| 13 | Total | Target | % | |||||||||||||||||||
| 14 | Total A: | 136 | 150 | 90.7% | ||||||||||||||||||
| 15 | Total B: | 75 | 96 | 78.1% | ||||||||||||||||||
| 16 | Total A+B: | 211 | 246 | 85.8% | ||||||||||||||||||
| 17 | ||||||||||||||||||||||
| 18 | Top 3 for A+B | % | ||||||||||||||||||||
| 19 | ||||||||||||||||||||||
| 20 | ||||||||||||||||||||||
| 21 | ||||||||||||||||||||||
Task Scores | ||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| Q5:R10,Q3:R3 | Q3 | =SUM(C3+E3+G3+I3+K3+M3+O3) |
| S3,S5:S10 | S3 | =SUM(Q3+R3) |
| C4:R4 | C4 | =SUM(C3)*6 |
| S4 | S4 | =SUM(Q4:R4) |
| T5 | T5 | =SUM(S5/S3) |
| T6 | T6 | =SUM(S6/S3) |
| T7 | T7 | =SUM(S7/S3) |
| T8 | T8 | =SUM(S8/S3) |
| T9 | T9 | =SUM(S9/S3) |
| T10:T11 | T10 | =SUM(S10/S3) |
| C11:S11 | C11 | =SUM(C5:C10) |
| Q14 | Q14 | =SUM(G11+I11+K11+M11+O11) |
| R14 | R14 | =SUM(Q4) |
| Q15 | Q15 | =SUM(D11+H11+J11+L11+N11+P11) |
| R15 | R15 | =SUM(R4) |
| Q16:R16 | Q16 | =SUM(Q14:Q15) |
| T14:T16 | T14 | =SUM(Q14/R14) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| T14:T16 | Cell Value | >=0.9 | text | NO |
| T14:T16 | Cell Value | <0.8 | text | NO |
| T14:T16 | Cell Value | between 0.8 and 0.89 | text | NO |
| T5:T11 | Other Type | Color scale | NO | |
| K15:K22 | Other Type | Color scale | NO | |
So based on the current data, I would like to auto populate cell M19 with the names as follows:
1. John, Peter 95.1%
M20 will be:
2. Karen 85.4%
and M21 will be:
3. Julie 82.9%
Is there a way I can achieve this? It will often be multiple people with the same score so I need to ability to return more than one name in the cells where this occurs.
I hope this makes sense.
Thank you as always for your help!
