Array-style ranking with SUMPRODUCT
AdvancedExcel has RANK.EQ and RANK.AVG for straightforward ranking. However, they don't work when you need to rank inside a group, like scoring each sales rep against their own region instead of the whole company. SUMPRODUCT has no such limit, because it lets you build a rank out of a count.
The table in A1:F13 shows quarterly sales for 12 reps across three regions. Some reps ended up with the same sales amount, so handling ties is part of the exercise.
Your task
Fill in three ranking columns, each one a step up from the last:
- Simple rank (D2:D13): rank every rep on Quarterly sales in column C, highest first. Reps with the same sales get the same position.
- Averaged rank (E2:E13): rank the same way, but tied reps split the positions they take up and each get the average.
- Regional rank (F2:F13): rank each rep only against the others in their Region from column B.
Work straight from the table fields, without helper columns, and aim for formulas that stay correct if the ties or the region boundaries change.
The first two columns are a warm-up. RANK.EQ and RANK.AVG answer them in one step, and you are free to use them. Try building them with SUMPRODUCT first though: the counting trick you work out there is exactly what column F needs, and no built-in rank function can do that one for you.
Need some help?
Hint 1
Think of a rank as a count. If three sales figures sit above this rep's, the rep is in fourth place. Comparing the whole sales range against a single cell gives you a TRUE/FALSE array, and SUMPRODUCT only adds those up once you turn them into numbers, for example by multiplying by 1.
Hint 2
Two reps with the same sales take up two positions in the ranking, but your Simple rank formula gives both of them the higher one. If they take up positions 2 and 3, Simple rank gives both a 2, while Averaged rank should give both a 2.5. To make that adjustment, you need to know how many reps share the same sales value, and COUNTIF can tell you that.
Hint 3
Regional rank is the same count with one extra condition. SUMPRODUCT multiplies arrays together, so a TRUE/FALSE region test multiplied by your TRUE/FALSE sales test keeps only the rows that pass both.
Related function(s)
Array-style ranking with SUMPRODUCT
AdvancedExcel has RANK.EQ and RANK.AVG for straightforward ranking. However, they don't work when you need to rank inside a group, like scoring each sales rep against their own region instead of the whole company. SUMPRODUCT has no such limit, because it lets you build a rank out of a count.
The table in A1:F13 shows quarterly sales for 12 reps across three regions. Some reps ended up with the same sales amount, so handling ties is part of the exercise.
Your task
Fill in three ranking columns, each one a step up from the last:
- Simple rank (D2:D13): rank every rep on Quarterly sales in column C, highest first. Reps with the same sales get the same position.
- Averaged rank (E2:E13): rank the same way, but tied reps split the positions they take up and each get the average.
- Regional rank (F2:F13): rank each rep only against the others in their Region from column B.
Work straight from the table fields, without helper columns, and aim for formulas that stay correct if the ties or the region boundaries change.
The first two columns are a warm-up. RANK.EQ and RANK.AVG answer them in one step, and you are free to use them. Try building them with SUMPRODUCT first though: the counting trick you work out there is exactly what column F needs, and no built-in rank function can do that one for you.
Need some help?
Hint 1
Think of a rank as a count. If three sales figures sit above this rep's, the rep is in fourth place. Comparing the whole sales range against a single cell gives you a TRUE/FALSE array, and SUMPRODUCT only adds those up once you turn them into numbers, for example by multiplying by 1.
Hint 2
Two reps with the same sales take up two positions in the ranking, but your Simple rank formula gives both of them the higher one. If they take up positions 2 and 3, Simple rank gives both a 2, while Averaged rank should give both a 2.5. To make that adjustment, you need to know how many reps share the same sales value, and COUNTIF can tell you that.
Hint 3
Regional rank is the same count with one extra condition. SUMPRODUCT multiplies arrays together, so a TRUE/FALSE region test multiplied by your TRUE/FALSE sales test keeps only the rows that pass both.