Adjusted Value without Percentage

[shmalex]

[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]8[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]2[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]33[/TD]
[TD]32[/TD]
[TD]27[/TD]
[TD]29[/TD]
[/TR]
</tbody>[/TABLE]

I have 5 groups of values, each column is a separate group. The bottom row is the total adjusted value for each of the columns above. I need to figure out the percentage that each row holds in getting to the adjusted total.

So for example the first column: (8*x)+(4*y)+(7*z)=30
Second column: (7*x)+(8*y)+(3*z)=33
Third column: (8*x)+(9*y)+(2*z)=32


I need to figure out what x, y and z are. I can add more values to the chart to increase the accuracy.

 

[Eric W]

Welcome to the forum.

If the columns are all independent, then there's no way to find unique values for x, y, and z. There are an infinity of answers. If you use 3 columns and want to find a common set of x, y, and z for all three columns, you can do it with the A6:A8 formula:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1.578947[/TD]
[TD="align: right"]1.833333[/TD]
[TD="align: right"]1.684211[/TD]
[TD="align: right"]1.6875[/TD]
[TD="align: right"]1.380952[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1.578947[/TD]
[TD="align: right"]1.833333[/TD]
[TD="align: right"]1.684211[/TD]
[TD="align: right"]1.6875[/TD]
[TD="align: right"]1.380952[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1.578947[/TD]
[TD="align: right"]1.833333[/TD]
[TD="align: right"]1.684211[/TD]
[TD="align: right"]1.6875[/TD]
[TD="align: right"]1.380952[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]-1.4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]3.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G1[/TH]
[TD="align: left"]=A$4/(SUM(A$1:A$3))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]=SUMPRODUCT(A1:A3,G1:G3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A6:A8[/TH]
[TD="align: left"]{=TRANSPOSE(MMULT(A4:C4,MINVERSE(A1:C3)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



If you put -1.4, 4, and 3.6 into all three of your equations, they will work. But by looking at 3 different columns, you'll get 3 different values.

If you really do want to want to treat each column independently, then you might be able to add constraints to get an answer. For example, if you set x=y=z, it's easy to solve: x=y=z=30/(8+4+7), which is what I show in column G. Other constraints might be to force x,y,z all positive, and have the smallest sum. It really depends on what you're looking for.

 

[shmalex]

Eric each column represents a different person in this problem, so for example: Column A is John and Column B is Daviceld.
Each row is a score that they scored on the same exam so for example Row 1 is the Math Exam, Row 2 is the English Exam
The bottom row is the total score that they all scored when you add up the adjusted value of each exam.

My question is what weight does each exam hold in the total adjusted value. So the weight for each row is the same. I have an unlimited number of exam takers to add to the formula but I want to use excel to estimate what weight each exam holds.

Thank you for your help.

 

[Eric W]

How do you get the adjusted score? Is each test weighted differently? 8+4+7=19, not 30. Is the weighting consistent across students? Does each test have the same possible maximum score?

 

[shmalex]

How do you get the adjusted score? Is each test weighted differently? 8+4+7=19, not 30. Is the weighting consistent across students? Does each test have the same possible maximum score?

Each test has a different weight, the weight is consistent across students. Yes all the tests have the same possible max. Just multiply the score by the weight to get the adjusted score. If you'd like I can send you the file, it may be easier to figure out that way.


Thanks again Eric for your help.

 

[Eric W]

Are those actual examples then? If the weights are the same for all students, then that implies that x,y,z are the same for all of them. If so, then you can solve for x,y,z from a series of linear equations derived from any 3 columns. This is what I did in post 2, giving x=-1.4, y=4, and z=3.6. If you put those values into your equations from post 1, you'll see that all of them work out correctly. The problem now is that if you use those same values in subsequent columns, they do not generate the given total adjusted value. Probably a bigger issue is that -1.4 makes no sense as a weighting factor. It implies that the better the score on test 1, the worse you'll get on the overall rating.

So either my model is wrong, or else the total adjusted value for each column in your example has not been calculated consistently. I even considered that there might be some rounding going on, so I set up a Solver problem to get the "best fit". Using all 5 columns, it came up with x=2.5, y=1.25, z=.4. It's a good fit for columns C:E, but A is off by 2, and B is off by 4. So that's probably not the issue.

 

[Dim Me as xlNoob]

Would it help to know what the maximum possible score is on each test and the maximum possible adjusted total? Is this information available?