Forum Discussion
Reverse Calculate Weighted Average Scores
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.
- Apologies 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<< The formula is less important to me than the actual solution >>
If you are thinking that there is one solution, that's wrong! At least, it's wrong, given the limited information (on constraints/boundaries/restrictions and relationships) that you have supplied to us. Mathematically, there are nine solutions, one for each of the nine variables (which are presumably independent, as you have not indicated otherwise)…but each of those nine solutions is dependent on the scores for the other eight concepts.
Again, the minimum score required for any concept depends upon the (weight and) score of every other concept. Grasp that. A person (or computer) can't determine the minimum for #1 without knowing (or assigning) values for #2-#9; a person (or computer) can't determine the minimum for #2 without knowing (or assigning) values for #1 and #3-#9. And so on.
As you have written that scores can only be in the range 0 to 4, the numbers I provided in green on Sheet2 are the 9 solutions — the minimum scores* one can have for those concepts and still reach the target weighted score. But each — individually — can only apply if scores for all other concepts are at their maximums (of 4).* in multiples of 0.01
In the newer attached workbook, I have added nine columns of content to Sheet2 to make this more obvious,
The best analogy for this situation I can think of is this:
You ask me to tell you which number between 1 and 1 thousand is most even. I then say that half of them (500) are even, so there are 500 such numbers, but you demand "one number". You have not specified sufficient constraints/boundaries/restrictions to arrive at just "one number".
Based on information you supplied, there are — among the 268 sextillion (268,101,567,757,470,981,763,601) possible combinations of scores — up to 111 trillion combinations of scores that might reach the target. Realistically, it's far, far less than 111 trillion, but no one is going to compute the validity of those combinations, much less determine (multiple) minimums among them. To reduce that number, you — or someone with knowledge of the problem domain — must devise additional constraints or boundaries or restrictions (choose your term) and/or limiting relationships between the scores, whether they are real or you make them up.
Maybe you have some ideas (vague or specific) of which possible values you want to check. To assist you in those checks, I created different minimization formulas on Sheet3. There you can enter whatever values you care about (for all 9 concepts under Scenario 0, or for 8 of the 9 concepts in the other scenarios) to see the results. And as on Sheet2, the cells highlighted in green will show the minimum score — rounded up to the next multiple of 0.01 — for that concept (row) with that combination of other, assigned concept scores (in the same column) required to reach the target.
If that is correct, I have completely misunderstood the question. It's not unusual...
