Build a Weighted Decision Matrix in Excel

A weighted decision matrix in Excel helps you compare options fairly: you score each option on a few criteria, give each criterion a weight, and let one formula add it all up. It is a common tool for choosing a supplier, a software product or a job offer, and a popular interview task. This guide builds a decision matrix in Excel from scratch with SUMPRODUCT, $ signs and INDEX MATCH.

What is a weighted decision matrix?#

A weighted decision matrix is a table that scores options against criteria, where some criteria count more than others. Each option gets a score per criterion, often from 1 (poor) to 5 (excellent). Each criterion has a weight, the share of the decision it controls, such as 50% for price.

The weighted score for an option is:

Score = (weight 1 × score 1) + (weight 2 × score 2) + (weight 3 × score 3)

This is the same maths as a weighted average. If you want the background, see how to calculate a weighted average in Excel.

How to set up the decision matrix#

Put the options in rows, the criteria in columns and the weights in a row of their own above the scores. This example compares four suppliers on price, quality and delivery:

Two rules keep the matrix fair:

1. Score every criterion so that higher is better. For price, a cheaper supplier gets a higher score, not a lower one.
2. Make the weights add up to 100%. Check with =SUM(B2:D2), which should return 1. Format row 2 as percentages if you prefer to see 50%, 30% and 20%. The SUM and AutoSum lesson covers this check.

How to calculate the weighted score with SUMPRODUCT#

SUMPRODUCT multiplies two ranges item by item and adds the results, which is exactly a weighted score. In E3, type:

=SUMPRODUCT($B$2:$D$2,B3:D3)

For Supplier A, Excel calculates 0.5×4 + 0.3×3 + 0.2×5 = 2.0 + 0.9 + 1.0 = 3.9.

Both ranges must be the same size: three weights and three scores. The SUMPRODUCT function guide explains the rules, and the full guide to SUMPRODUCT shows other uses, such as conditional totals.

You could write the long version, =B$2*B3+C$2*C3+D$2*D3, but it gets harder to read and easier to break with every criterion you add.

Why the weights need $ signs#

The $ signs make the weight row an absolute reference, so it stays fixed when you copy the formula. Select E3:E6 and fill down (Windows: Ctrl+D; Mac: ⌘D). Each row then reads:

Without the $ signs, the second formula would point at B3:D3 as the weights, and every score below row 3 would be wrong. To add them quickly, select the reference while typing and press F4 (Mac: ⌘T). The lesson on relative and absolute references practises exactly this.

How to find the winner with INDEX MATCH#

INDEX MATCH can return the name of the option with the highest score, so the answer updates when scores change. In any empty cell, type:

=INDEX(A3:A6,MATCH(MAX(E3:E6),E3:E6,0))

It works from the inside out:

• MAX(E3:E6) finds the highest score, 3.9.
• MATCH(3.9,E3:E6,0) finds its position in the list, 1. The 0 means an exact match.
• INDEX(A3:A6,1) returns the first name, Supplier A.

The INDEX function guide and MATCH function guide explain each part, and the INDEX MATCH guide covers more lookups. If two options tie, MATCH returns the first one, so check for ties before you announce a winner.

To rank every option, add a column with =RANK.EQ(E3,$E$3:$E$6) and fill down: Supplier A ranks 1, B ranks 2, C ranks 3 and D ranks 4. See how RANK works in Excel for tie handling.

How to test whether the result is robust#

Change the weights and see whether the winner changes. This is called a sensitivity check. Scores of 3.9, 3.8 and 3.7 are close, so small changes in weights could flip the result.

Try making quality the main criterion: Price 0.3, Quality 0.5, Delivery 0.2. The scores become:

Supplier B now wins. Because the formulas point at the weight row, you only change three cells and everything else updates. If the winner flips this easily, agree on the weights with the decision-makers before you score, not after. Purchasing teams use this approach a lot, as described in Excel skills for procurement jobs.

Common mistakes in a decision matrix#

These slips make a matrix give the wrong answer:

1. Weights that don't add up to 100%. The ranking still works, but totals are hard to compare. Check with SUM.
2. Mixed score directions. If a low price gets a low score, cheap options are punished. Make higher always mean better.
3. Missing $ signs. The weight reference moves as you fill down. Use $B$2:$D$2.
4. Different range sizes. =SUMPRODUCT($B$2:$D$2,B3:C3) returns #VALUE!, because three weights don't pair with two scores.
5. Typing the winner by hand. Use INDEX MATCH so the answer follows the numbers.

Practise it#

A decision matrix is three core skills in one: SUMPRODUCT, absolute references and INDEX MATCH. Practise INDEX and MATCH in a live spreadsheet: you type each formula and it is checked instantly. Then try the exercise on this page to calculate Supplier A's weighted score yourself.