Forum Discussion
Adam08780
Dec 15, 2023Copper Contributor
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 f...
SnowMan55
Dec 17, 2023Bronze Contributor
Writing Excel formulas using the LET function helps to make the calculations clearer. It requires Excel version 2021 or later.
See the attached workbook. Be sure to read the notes on the _Info worksheet.
- HansVogelaarDec 17, 2023MVP
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 - SnowMan55Dec 17, 2023Bronze Contributor
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.
- Adam08780Dec 17, 2023Copper ContributorApologies but this seems to complicated than it needs to be. Sorry if I haven't explained it well enough. I'm looking for the minimum score required across all 9 concepts weighed differently in order to achieve overall 3.75 for 2024 as a random example below:
Concept Weighed 2022 2023 2024
1 10% 3.75 3.92 3.92
2 10% 2.82 3.76 3.9
3 10% 3.33 3.24 3.75
4 12% 3.35 3.5 3.6
5 11% 3.46 3.49 3.7
6 10% 3.7 3.75 3.75
7 18% 3.71 3.71 3.73
8 15% 2.93 3.71 3.71
9 4% 4 3.93 4
Average 100% 3.41 3.58 3.75
The formula is less important to me than the actual solution