Hello! I've got a fun one. I need a formula for column G that I can paste into G2 daily and copy down a spreadsheet that will calculate the rank of each row within each column C, D, E, F and display the average rank of that row's values within each of those columns. It should only consider values that are in the same location, column A. So column A is the criteria. Pretty sure the function example below doesn't really exist, but just for visual reference I typed out what I'm trying to do, similar to countifs formulas I have elsewhere in the sheet, but this time for rank, with a criteria range, criteria, rank range, etc.
FYI, the names in column B aren't to be considered in the formula.
Like this:
= AVERAGE(RANKIFS(A$2:A$1500,A2,C$2:C$1500),RANKIFS(A$2:A$1500,A2,D$2:D$1500),RANKIFS(A$2:A$1500,A2,E$2:E$1500),RANKIFS(A$2:A$1500,A2,F$2:F$1500))
FYI, the names in column B aren't to be considered in the formula.
Like this:
= AVERAGE(RANKIFS(A$2:A$1500,A2,C$2:C$1500),RANKIFS(A$2:A$1500,A2,D$2:D$1500),RANKIFS(A$2:A$1500,A2,E$2:E$1500),RANKIFS(A$2:A$1500,A2,F$2:F$1500))
| Book1 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Location | Average Rank | |||||||
| 2 | 2 | Steph | 96.00 | 93.00 | 94.50 | 94.50 | |||
| 3 | 2 | Rach | 96.00 | 92.00 | 94.00 | 94.00 | |||
| 4 | 2 | Jes | 106.00 | 79.25 | 92.62 | 92.62 | |||
| 5 | 7 | Pete | 83.00 | 94.00 | 88.50 | 90.33 | |||
| 6 | 7 | Suzy | 105.00 | 89.00 | 97.00 | 94.33 | |||
| 7 | 7 | Henry | 99.00 | 93.00 | 96.00 | 95.00 | |||
| 8 | 8 | etc | 95.00 | 83.00 | 89.00 | 91.00 | |||
| 9 | 8 | etc | 93.00 | 94.00 | 93.50 | 93.50 | |||
| 10 | 9 | etc | 93.00 | 91.00 | 92.00 | 92.00 | |||
| 11 | 9 | etc | 98.00 | 88.00 | 93.00 | 94.67 | |||
| 12 | 2 | etc | 88.00 | 93.00 | 90.50 | 90.50 | |||
Sheet1 | |||||||||
Thank you!
