Question: I need to create a sumproduct formula for a weighted average. I only have one cell per criteria and multiple criteria for 4 different vehicles.
I need to create a sumproduct formula for a weighted average. I only have one cell per criteria and multiple criteria for 4 different vehicles. How do I construct it. There is a weighted score falls in Cells B2-G2 for each criteria. I scored 4 vehicles B7-G7, B8-G8, etc. I am not sure if I am weighting all the criteria in column, b, c, d, e, f, g. or create the sum product formula for each vehicle from left to right?
Open Microsoft Office Excel.
Create a matrix (table) as shown above.
Enter your score between 1 and 5 for one criterion (decision making factor) for different choices (options; car types) at a time. That is, score all choices for a criterion before moving to the next criterion.
In Your Score Weighted row, enter the formula to calculate the weighted score for Price criterion using the SUMPRODUCT function. Copy the formula to the cells for the weighted score of other criteria.
| Your Score Weighted | ||||||
| Weight | 5 | 5 | 4 | 3 | 2 | 4 |
| Price | Ability to carry cargo such as a canoe | Fuel efficiency | Comfort over long distances | Good looks and fun | Quality build/ manufacturers reputation | |
| Criteria\Choice | Your Score | |||||
| Mini Van | 5 | 4 | 2 | 4 | 2 | 4 |
| SUV | 3 | 4 | 3 | 3 | 3 | 4 |
| Four-door sedan | 2 | 2 | 3 | 3 | 3 | 4 |
| Sports car | 5 | 1 | 5 | 2 | 5 | 4 |
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts