SORTBY, SEQUENCE and LET

Rank a league table with SORTBY, number it with SEQUENCE, and name the parts of a formula with LET. Advanced · 5 min · SORTBY, SEQUENCE, LET, Calculated arrays

Three more tools complete the dynamic array kit:

SORTBY sorts a range by another range, or by a calculation
SEQUENCE generates a list of numbers
LET gives names to parts of a formula, so long formulas read clearly and repeated parts are worked out once

Today's data is a pub quiz league. Not every team has played the same number of games, which will matter.

Steps

Rank the teams by points, highest first. In F2, use =SORTBY(A2:A9, C2:C9, -1): sort the names in column A by the points in column C. The -1 means descending.
Number the places. In E2, =SEQUENCE(8) spills the numbers 1 to 8 down the column, one for each team.
Raw points favour teams that have played more. In G2, rank the teams by points per game instead. SORTBY can sort by a calculation: =SORTBY(A2:A9, C2:C9/B2:B9, -1)
How spread out is the league? In B11, find the best points per game minus the worst. Instead of typing C2:C9/B2:B9 twice, name it once with LET: =LET(ppg, C2:C9/B2:B9, MAX(ppg)-MIN(ppg))

Remember

SORTBY(array, by_array, -1) sorts by another range, or by a calculation like C2:C9/B2:B9.
SEQUENCE(n) spills the numbers 1 to n.
LET(name, value, calculation) names part of a formula so it is written, and worked out, once.

Previous: FILTER: rows that match · Next: Moving and selecting at speed