Least Squares optimization in excel
Posted on 2014-10-20
I would like to perform a least squares optimization in excel using solver.
I tried the following set-up:
- Given is a vector of original exposure across a range of seven nodes (C8:I2)
- The aim is to replicate this exposure at each point as close as possible from a set of 5 instruments. The contribution of each instrument on any of the 7 nodes is respresented in the matrix C11:I15
- I try to solve for the number of units of each instrument (K11:K15) such that the sum of the squared differences (cell C24) is minimized , using solver
The issue is that the solution returned by solver is fairly poor in terms of fit/node (C20:I20). As a check, I have used Excel's linest functions, and the fit appears much better (S9:S14, which indicate a much better fit per node than the solver-based solution as displayed in row B20-H20).
Any idea as to how I may improve the set-up of the Lsq-optimization using excel would be appreciated