As seen in table above I have some data, elements of which form part of different products. For example, 4th element in above table (highlighted row) i.e, 44 is part of product Q, S and Y.
Each product has an average as below :
average of Product A=(17+22+21)/3=20
average of Product Q=(44+32+23)/3=33
average of Product S=(44+32+23+56+73+12)/6=40
average of Product Y=(17+22+21+44+32+23+56+73+12+26+45+37)/12=34
Now I want to manipulate the data such that new averages are
Average of product A= 25
Average of product Q = 35
Average of Product S= 32
Average of product Y= 39
So result should be :
Currently I have a VBA code that loops over each element multiple times to calculate difference between current and new averages and apply this difference to elements.
Is there any matrix algorithm I can use that would make the same calculations instead of using iterative loops ? Algorithm code will be implemented in Matlab, C++ or Python or VBA.
Currently I have been exploring MATLAB function lsqlin https://www.mathworks.com/help/optim/ug/lsqlin.html but cannot get it to do what I want.
Could you please help ?
Source: Windows Questions C++