Calculate weighted scores for vendor evaluation
IntermediateWhen evaluating vendors, not all criteria are equally important. Price might matter more than delivery time, or quality might be the top priority. Weighted scoring solves this by assigning an importance weight to each criterion and calculating a composite score.
SUMPRODUCT is the ideal function for weighted scoring. It multiplies corresponding elements in the arrays you provide and returns the sum of those products. The result is a single score that reflects each vendor's performance across all criteria, with the most important criteria carrying more influence.
The syntax is:
=SUMPRODUCT(array1, array2)
- array1: the first range of values to multiply
- array2: the second range of values to multiply (must be the same size as array1)
For example, =SUMPRODUCT(A1:A3, B1:B3) multiplies A1 * B1, A2 * B2, and A3 * B3, then adds up those three products.
What you need to do
The spreadsheet has two sections:
- Weights in row 2: the importance of each criterion as a decimal. These add up to 1.00.
- Vendor scores in rows 5 through 8: each vendor has a rating from 1 to 10 on each criterion.
In column F, calculate the weighted score for each vendor using SUMPRODUCT. Multiply each vendor's criterion scores by the corresponding weights and sum the results.
Use a single formula in F5 that you can fill down through F8. Because the formula needs to reference the same weight row for every vendor, use absolute references to lock the weight row while letting the vendor score row adjust as you fill down.
When you are done, each vendor's weighted score will be a number between 1 and 10 reflecting how well they match your priorities.
Need some help?
Hint 1
SUMPRODUCT needs two arrays of the same size: the weights row (B2:E2) and a vendor's scores row.
Hint 2
Lock the weights row with absolute references using B$2:E$2 so the same weights are used when you copy the formula down for each vendor.
Related function(s)
Calculate weighted scores for vendor evaluation
IntermediateWhen evaluating vendors, not all criteria are equally important. Price might matter more than delivery time, or quality might be the top priority. Weighted scoring solves this by assigning an importance weight to each criterion and calculating a composite score.
SUMPRODUCT is the ideal function for weighted scoring. It multiplies corresponding elements in the arrays you provide and returns the sum of those products. The result is a single score that reflects each vendor's performance across all criteria, with the most important criteria carrying more influence.
The syntax is:
=SUMPRODUCT(array1, array2)
- array1: the first range of values to multiply
- array2: the second range of values to multiply (must be the same size as array1)
For example, =SUMPRODUCT(A1:A3, B1:B3) multiplies A1 * B1, A2 * B2, and A3 * B3, then adds up those three products.
What you need to do
The spreadsheet has two sections:
- Weights in row 2: the importance of each criterion as a decimal. These add up to 1.00.
- Vendor scores in rows 5 through 8: each vendor has a rating from 1 to 10 on each criterion.
In column F, calculate the weighted score for each vendor using SUMPRODUCT. Multiply each vendor's criterion scores by the corresponding weights and sum the results.
Use a single formula in F5 that you can fill down through F8. Because the formula needs to reference the same weight row for every vendor, use absolute references to lock the weight row while letting the vendor score row adjust as you fill down.
When you are done, each vendor's weighted score will be a number between 1 and 10 reflecting how well they match your priorities.
Need some help?
Hint 1
SUMPRODUCT needs two arrays of the same size: the weights row (B2:E2) and a vendor's scores row.
Hint 2
Lock the weights row with absolute references using B$2:E$2 so the same weights are used when you copy the formula down for each vendor.