Excel weighted score: criteria, weights and ranking checks

An Excel weighted score is the sum of comparable criterion scores multiplied by their assigned weights. Normalize the criteria, make higher scores consistently mean better outcomes, and check how the ranking changes when decision priorities change.

A scorecard makes a business choice inspectable. It does not turn preferences into objective facts: the criteria, weights and scoring rules still require an accountable owner.

Start with the decision and the source rows

Consider a synthetic US manufacturer choosing among three suppliers. Procurement supplies quoted unit costs and lead times; operations supplies on-time delivery percentages for the same evaluation window. All quotes cover the same item specification, delivery location and purchasing terms.

Use one row per eligible supplier. A supplier that fails a mandatory requirement should be reviewed through that requirement before ranking. A high weighted score should not compensate for a failed mandatory qualification.

Place supplier names in A and the raw measures in B:D:

The quoted prices are inputs, not measures of total supplier profitability. Document whether freight, rebates and minimum quantities are included. Use the same definition for every supplier.

This decision score differs from a weighted average in Excel, where revenue or volume supplies the weights. Here, people choose the weights to express the decision's priorities.

Normalize different measures to a common scale

Use a relative scale from 0 to 100. The best observed value gets 100 and the worst gets 0 for each criterion. This is a ranking convention for these alternatives, not a statement that the worst supplier has no business value.

Higher-is-better score = 100 × (Value − Minimum) / (Maximum − Minimum)

Lower-is-better score = 100 × (Maximum − Value) / (Maximum − Minimum)

Cost and lead time are lower-is-better. On-time delivery is higher-is-better. Put the normalized results in E:G. Before calculating, confirm every raw cell contains a number:

=COUNT(B2:D4)=ROWS(B2:D4)*COLUMNS(B2:D4)

Then enter these formulas in row 2 and fill down to row 4:

E2: =IF(MAX($B$2:$B$4)=MIN($B$2:$B$4),NA(),100*(MAX($B$2:$B$4)-B2)/(MAX($B$2:$B$4)-MIN($B$2:$B$4)))
F2: =IF(MAX($C$2:$C$4)=MIN($C$2:$C$4),NA(),100*(C2-MIN($C$2:$C$4))/(MAX($C$2:$C$4)-MIN($C$2:$C$4)))
G2: =IF(MAX($D$2:$D$4)=MIN($D$2:$D$4),NA(),100*(MAX($D$2:$D$4)-D2)/(MAX($D$2:$D$4)-MIN($D$2:$D$4)))

The NA result intentionally stops the score when all values are equal. Decide on the treatment before replacing it. Do not silently treat an unresolved criterion as zero.

For Birch, the cost score is 100 × (120 − 110) / (120 − 100) = 50. Its on-time score is 100 × (99% − 96%) / (99% − 96%) = 100. Its lead-time score is 100 × (5 − 4) / (5 − 3) = 50.

Apply weights and prove the result

Enter 50%, 30% and 20% in E7:G7 for cost, on-time delivery and lead time. The weights total 100%. Put the weighted score in H2 and rank in I2, filling both through row 4:

H2: =SUMPRODUCT(E2:G2,$E$7:$G$7)
I2: =RANK.EQ(H2,$H$2:$H$4,0)
Weight check: =ABS(SUM(E7:G7)-1)<0.000000001

Microsoft documents SUMPRODUCT as adding the products of corresponding array entries. Keep score and weight ranges the same size. RANK.EQ with order zero ranks the largest value first and gives equal values equal ranks. Covirage is not affiliated with Microsoft.

Birch's proof is 50 × 0.50 + 100 × 0.30 + 50 × 0.20 = 65. Recompute the total independently in J2:

=H2-(E2*$E$7+F2*$F$7+G2*$G$7)

Fill down; each difference should be zero within spreadsheet precision. All three final scores must lie between 0 and 100 when their normalized inputs do and the weights are nonnegative and sum to one.

Test whether the winner depends on priorities

Keep the raw measures and normalization unchanged. For a second scenario, raise the cost weight to 70%, reduce on-time delivery to 10%, and keep lead time at 20%. Put this weight set in E8:G8 and compute:

=SUMPRODUCT(E2:G2,$E$8:$G$8)

Alder now wins. With lead time fixed at 20% and the remaining 80% split between cost and on-time delivery, let w be the cost weight. Alder scores 100w; Birch scores 50w + 100(0.80 − w) + 50(0.20), or 90 − 50w. They tie when w = 0.60, at a score of 60.

That threshold is more informative than a single winner. Ask the decision owner whether cost should receive more or less than 60% of the weight under this particular scoring convention. Use scenario analysis in Excel for the broader assumptions review.

Where it goes wrong

• Mixed directions. Giving expensive suppliers higher cost scores reverses the intended preference.
• Blank inputs. SUMPRODUCT treats nonnumeric array entries as zero. Validate completeness first so missing evidence cannot quietly become poor performance.
• Double counting. Delivery reliability and late-delivery frequency may reward the same underlying outcome twice.
• Changing the comparison set. Adding a supplier changes observed minima and maxima. Freeze the evaluation set or use agreed external scoring anchors when comparing decisions over time.
• False precision. Tiny score differences can reflect subjective weights or uncertain input estimates. Keep full precision for ranking but show only useful display precision.

A recurring scorecard also needs ownership of its source fields. The CRM data quality scorecard illustrates a different scorecard purpose: checking records rather than selecting suppliers.

Compare your supplied scorecards in Covirage

Bring your workbook with raw supplier measures, normalization rules, weights and completed scores. Covirage maps the columns, its tools compute and check the comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, set up your business and bring a sample of your own data.

See self-service analytics for the supporting approach to business-file analysis.