Weighted decision matrix in Excel: a worked example

Step 1: criteria and weights that add up to 100%

Pick four to six criteria that really separate the options, and agree on the weights before anyone looks at the offers. Here cost gets 35%, features 30%, support 20% and speed of go-live 15%. A check cell =SUM(B6:E6) must show 100%, because with weights that add up to 100% the weighted score stays on the same 0–10 scale as the inputs.

If a team hands you importance points instead (say 7, 3, 6 and 4), divide each by the total: =B2/SUM($B$2:$E$2). A data validation rule that rejects weights above, say, 40% stops one criterion from deciding everything on its own.

Step 2: put every criterion on the same 0–10 scale

You cannot add $48,000 to 4.1 stars, so each criterion is first normalised. Min-max scaling gives the best option 10, the worst 0 and the others a proportional score:

• Higher is better (features, support): =(D2-MIN(D$2:D$5))/(MAX(D$2:D$5)-MIN(D$2:D$5))*10
• Lower is better (cost, weeks to go live): =(MAX(B$2:B$5)-B2)/(MAX(B$2:B$5)-MIN(B$2:B$5))*10

The mixed references (B$2:B$5) keep the data range fixed when you copy the formula down. Northdesk costs $48,000, between the cheapest offer at $39,500 and the dearest at $56,000, so its cost score is (56,000 − 48,000) ÷ 16,500 × 10 = 4.8.

Step 3: the weighted score with SUMPRODUCT

=SUMPRODUCT(B9:E9,$B$6:$E$6) multiplies the four scores of a vendor by the four weights pair by pair and adds the products. Lock the weight row with $ and fill down. Salesgrove scores 8.5 × 35% + 3.3 × 15% + 6.0 × 30% + 7.5 × 20% = 6.77, Clientra 6.50, Northdesk 4.65 and Pipewell 3.50. To learn more about the function itself, see SUMPRODUCT with multiple criteria.

The winner's name comes from =INDEX(A9:A12,MATCH(MAX(F9:F12),F9:F12,0)), the rank of any vendor from =RANK.EQ(F10,F9:F12). In Excel 365, =SORTBY(A9:A12,F9:F12,-1) spills a leaderboard that re-sorts itself whenever a score or weight changes.

Worked example: choosing a CRM vendor

Raw offers and weights are yellow inputs. Rows 9–12 turn them into 0–10 scores and the weighted total, rows 15–19 pick the winner and measure how close the race is.

Worked example: choosing a CRM vendor

	A	B	C	D	E	F
1	CRM vendor	Annual cost ($)	Go-live (weeks)	Features (of 60)	Support (1–5)	Check
2	Northdesk	48,000	10	42	4.1
3	Pipewell	39,500	14	36	3.8
4	Clientra	56,000	8	51	4.6
5	Salesgrove	42,000	12	45	4.4
6	Weight	35%	15%	30%	20%	100%
7
8	Score 0–10	Cost (lower better)	Speed (lower better)	Features	Support	Weighted score
9	Northdesk	4.8	6.7	4.0	3.7	4.65
10	Pipewell	10.0	0.0	0.0	0.0	3.50
11	Clientra	0.0	10.0	10.0	10.0	6.50
12	Salesgrove	8.5	3.3	6.0	7.5	6.77
13
14	Result					Value
15	Winner (highest weighted score)					Salesgrove
16	Winning score					6.77
17	Runner-up score					6.50
18	Winning margin (points of 10)					0.27
19	Rank of Pipewell, the cheapest vendor					4

Fictional vendors and illustrative numbers.

Step 4: read the margin, not just the winner

The winning margin is =LARGE(F9:F12,1)-LARGE(F9:F12,2): 6.77 − 6.50 = 0.27 points on a 10-point scale. That is close. Move the weights from 35% cost / 30% features to 30% / 35% and Clientra wins with 7.00 against 6.65. Solving for the exact tipping point, a shift of just 2.2 percentage points from cost to features is enough. A result that flips on such a small change in judgement should be reported as a tie between two good options, not as a clear win.

Excel's Data Table or Goal Seek can find the tipping weight for you: set the difference between the two scores to zero by changing the cost weight. For the general technique see named ranges in Excel, which make weight formulas much easier to read.

Min-max is strict: the scoring method can change the winner

Pipewell is the cheapest vendor, but min-max gives it 0 on every other criterion because it is the worst on each, so it finishes last with 3.50. Clientra gets a cost score of 0 although it is only 42% dearer than Pipewell. A gentler scale, ratio to best (=MIN(B$2:B$5)/B2*10), would give Clientra 7.1 for cost instead of 0 and change the ranking. Neither method is wrong, but they answer different questions, so choose one before the offers arrive and use it for every option.

Knock-out criteria and several evaluators

Some requirements are not a matter of weight: a vendor without single sign-on, or a candidate without a work permit, is out whatever the score. Test those first with AND and gate the score: =IF(AND(G2="Yes",H2="Yes"),SUMPRODUCT(…),0). When several people score the options, average their scores per criterion and flag rows where the standard deviation is high, because a disagreement is worth a discussion before it disappears into an average.

Common mistakes

• Weights that do not add up to 100%. Scores then drift off the 0–10 scale and options scored with different weight sets cannot be compared. Add a check cell.
• Adding raw values in different units. Dollars, weeks and stars must be normalised to one scale first, or the criterion with the biggest numbers wins.
• Forgetting to flip lower-is-better criteria. The cheapest option and the fastest go-live must get the highest score, not the lowest.
• Relative references in SUMPRODUCT. Without $B$6:$E$6 the weight range slides down when you fill the formula and the lower rows multiply by empty cells.
• Setting the weights after seeing the scores. It is easy to tune a matrix until a favourite wins. Agree weights and scoring method first.
• Reporting a 0.27-point win as decisive. Always show the margin and how far a weight must move to change the winner.