Message Font: Serif | Sans-Serif
No. of Recommendations: 1

I like math, but it is not my profession nor am I formally educated in the field, so take what I say with a grain of salt. Having said that, I agree that what you are trying to do is a least squares type of problem. And since you are using a linear combination of polynomials a linear programming tool should do the trick. Depending upon your exact goals, a decent tool might be the Solver Add-in in Excel. This may be what Trick was describing.

Here is quick and dirty model I made from what you provided. I set your variables a-f in cells A1-F1 and assigned them dummy values of 1 (Solver will change them later). In cells A6:H10, I posted the data from your example table. In column I, I placed the formula you provided referencing the variables a-f and the values from the table for each row (except variable j, which is the formula output). I then added another formula in cells J6:J10 that represented the squared difference between the formula output (col I) and the target output (variable j in column H) for each row. In cell J11, I summed all the squared differences from the individual rows (J6:J10). The goal is to minimize the value of this cell by changing the values in cells A1:F1.

Using solver, I set the target cell to J11, set it to minimize and set the changing cells to A1:F1. The only thing left to do is to set the constraints which are dependent upon what it is you are trying to do. For example, is it OK for the values of A1:F1 to be <0? non-integer? >100? etc. Once the constraints are entered, hit Solve.

As a linear programming tool, Solver isn't very robust. The one issue I ran into is Excel doesn't like to raise 0 to a power and so if Solver tries 0 as a base it chokes and stops without returning an answer. So, if it doesn't work for you, you can check out other linear programming utilities. However, I have no advice for you in that realm as I have never used anything else.

Hope this helps!

Print the post  


When Life Gives You Lemons
We all have had hardships and made poor decisions. The important thing is how we respond and grow. Read the story of a Fool who started from nothing, and looks to gain everything.
Contact Us
Contact Customer Service and other Fool departments here.
Work for Fools?
Winner of the Washingtonian great places to work, and Glassdoor #1 Company to Work For 2015! Have access to all of TMF's online and email products for FREE, and be paid for your contributions to TMF! Click the link and start your Fool career.