Array Functions
Advanced
Capstone: score the suppliers
One weighted score per supplier, then rank them.
Task:
Procurement scored three suppliers on price, quality and speed, and those criteria do not matter equally. In column E give each supplier's weighted score using the weights in row 6, and in column F their rank with 1 being best.
Learning Objectives:
- Weight several criteria with SUMPRODUCT
- Anchor a shared row of weights
- Rank a computed column
Hints
Solve without hints for +5 XP
Interactive Spreadsheet
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Supplier | Price | Quality | Speed | Weighted | Rank |
| 2 | Alders | 7 | 9 | 6 | ||
| 3 | Brant | 9 | 6 | 8 | ||
| 4 | Corvo | 6 | 8 | 9 | ||
| 5 | ||||||
| 6 | Weights | 0.5 | 0.3 | 0.2 |
What this exercise teaches (contains the answer)
SUMPRODUCT replaces three multiplications and an addition with one function, and stays right when a fourth criterion is added. The anchoring is what makes it survive being copied: the score row moves down while the weights row must not, which is exactly the distinction the $ signs encode.