Forum Discussion
Reverse Calculate Weighted Average Scores
I'm trying to reverse calculate the average for year 2024 I want to be 3.75 of 9 concepts weighted differently. More of a maths question I suppose but the formula would help as well as mathematical formula. Each weighing has a maximum score of 4. Please advise.
| Concept | Weighed | 2022 | 2023 | 2024 |
1 | 10% | 3.75 | 3.92 | |
2 | 10% | 2.82 | 3.76 | |
3 | 10% | 3.33 | 3.24 | |
4 | 12% | 3.35 | 3.5 | |
5 | 11% | 3.46 | 3.49 | |
6 | 10% | 3.7 | 3.75 | |
7 | 18% | 3.71 | 3.71 | |
8 | 15% | 2.93 | 3.71 | |
9 | 4% | 4 | 3.93 | |
| Average | 100% | 3.41 | 3.58 | 3.75 |
But the weighted average of your result is 1.84, not 3.75
If you want to minimize the sum of the amounts while keeping the weighted average to 3.75, you have to assign the max value of 4 to the rows with the highest percentages until you cannot do that anymore, then fit the amount for the next lower (or equal) percentage to arrive at 3.75.
Concept Weighed 2022 2023 2024 1 10% 3.75 3.92 4 2 10% 2.82 3.76 4 3 10% 3.33 3.24 4 4 12% 3.35 3.5 4 5 11% 3.46 3.49 4 6 10% 3.7 3.75 3.1 7 18% 3.71 3.71 4 8 15% 2.93 3.71 4 9 4% 4 3.93 0 Average 100% 3.41 3.58 3.75 HansVogelaar Cell F11 contains the target (read the formulas), not the weighted average of the cells above it. That usage makes the formulas reusable for other targets. As indicated in the formulas, and as you also write explicitly, all other concepts must have a value of 4 for these minimums to work.
There are infinitely many solutions.
An easy one: 37.5 for concept 1, and 0 for concepts 2 to 9.
Another one: 3.75 for concepts 1 to 9.
Etc. etc.
- Apologies, I should have mentioned I'm looking for the minimum score needed per weighted concept to achieve 3.75 overall average score. Please advise
How do you define minimum score?
