Is there an algorithm to average using different constraints on data?

Product Product Product Current
Y A 17
Y A 22
Y A 21
Y S Q 44
Y S Q 32
Y S Q 23
Y S B 56
Y S B 73
Y S B 12
Y C 26
Y C 45
Y C 37

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 :

Product Product Product New
Y A 22
Y A 27
Y A 26
Y S Q 46
Y S Q 34
Y S Q 25
Y S B 38
Y S B 55
Y S B -6
Y C 57
Y C 76
Y C 68

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++

LEAVE A COMMENT