Forum Discussion
Reverse Calculate Weighted Average Scores
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.