Google-sheets – Assigning rank in another sheet based on Average

formulasgoogle sheetsgoogle-sheets-arrayformula

I have a list of names with an average beside it for every day Monday-Friday. A subgroup of names within this list will be highlighted. I want a macro to assign ranks of the highlighted individuals based on their average and place a value of their rank in a row of another sheet where each individual has a column. There is one row for each day.

Example. 5 people highlighted in a list of July 1st. Macro looks at other sheet (called Workbook) and finds row corresponding to July 1st. Then finds column corresponding to person with lowest average. Puts 1 in their cell. Repeats this with 2, 3, 4, 5. If 8 people highlighted would do this 1 through 8.

Link to a copy of the sheet
https://docs.google.com/spreadsheets/d/1DZif3MWw4xnyrK54OSm-htIty4G9XwMNZstROzaFk94/edit?usp=sharing

hope this makes sense

Best Answer

  • paste in P8 cell, then select P8 press CTRL + C and paste in S8, V8, Y8, etc.

    =ARRAYFORMULA(IFERROR(SORT(TRANSPOSE({'Workbook '!$E2:$P2; RANK(INDIRECT("Workbook !"&
 ADDRESS(MATCH(P5, 'Workbook '!$D:$D, 0), 5, 4)&":"&
 ADDRESS(MATCH(P5, 'Workbook '!$D:$D, 0), 16, 4))*1, INDIRECT("Workbook !"&
 ADDRESS(MATCH(P5, 'Workbook '!$D:$D, 0), 5, 4)&":"&
 ADDRESS(MATCH(P5, 'Workbook '!$D:$D, 0), 16, 4))*1)}), 2, 1)))

enter image description here

Related Topic