Matrix Adjustment

[srikanthk]

Hi Friends,

Is it possible to fill up (5% error acceptable) the cells in "Estimated Matrices" base on cell ratio of "Base Matrices" by matching both Row & Column total (which are fixed) of “Estimated Matrices" I have attached file for your reference. Please find the attachment.


Thanks in advance.

Regards,
srikanthk

[Kaper]

It is a good task for solver tool (If you indeed have excel 2003 as your profile states - you probably has to install it first from software distribution CD. In any case if you do not see Solver check with your version help how to install/activate it).


See attached file - I added only formulas summing rows and columns K10 and copy right and Q4 and copy down:

Please Login or Register  to view this content.

Then added squared of differences between a sum for row/column and required values in Q10:

Please Login or Register  to view this content.

This cell is used as a goal cell for solver and a goal is to minimize this cell.

Now three options:

A) If you want best martix - use as a first estimate your sum/number of cells (so =P9/25) value (in sample case 2430) and write it in all K4:O8 cells. These cells will be changed cells for solver (see Sheet1)

B) If you want proportional change write in a cell a common factor (start with 1) - for instance in cell H14. This cell will be the only changed cell for solver. In K8 write formula

Please Login or Register  to view this content.

and copy it to whole K4:Q8 range. See Sheet2.

C) As B but the aim is to minimize differences in all sums, while applying the same factor use cell H14 as above, cells K11 and right, R4 and down and R11 formulas:

Please Login or Register  to view this content.

R11 as goal cell and the goal is to minimize it. (Sheet 3)

I expect Sheet1 is what you look for, but then, there is no need to know base matrice, so I added B and C versions.

[srikanthk]

Dear Sir,

Sorry for late reply from my side.

Thanks for your help and suggestions!! Really appreciate!!

Regards,
srikanth