Curve fitting/goal seek problem

**rambochuck** · 03-31-2008, 01:20 PM

Hi, I would be grateful if anyone could give me some ideas how to get round this problem I have.

I have a large amount (~400) of data series, each consisting of about 900 X and Y values. For each set of X and Y values, I want to compare their plot to the equation Y=A*(X^(2/3)) , where A is a constant for that particular pair of series, and then choose the A value which has the smallest RMS (root mean square) error to the plot of X and Y.

This is fine for one pair of X and Y values - I simply plot a table of of new Y values based on different A values (from 0 to -1) and choose the one with the lowest RMS error. However, just for one data series this takes at least 10 minutes - how can I at least partly automate this process? I have hardly any programming knowledge but am farily competant with forumlae in excel. I have been told goal seek might be the solution to my problem?

Thanks in advance

**shg** · 03-31-2008, 02:38 PM

If the x values are in A1:A400, and Y is in B1:B400, then in C1 and copy down =A1 ^ (2/3)

Then in some vacant cell, =LINEST(B1:B400, C1:C400, FALSE) to get the value of the best-fit coefficient.

Edit: The last argument results in the best fit for y = az (where z = x^(2/3) ). If the last argument is TRUE or omitted, it computes the best fit to y = az + b. LINEST also returns the value of b, along with other metrics of the fit, if it is array-entered.

**rambochuck** · 03-31-2008, 07:37 PM

Excuse my ignorance, what do you mean by 'array entered'?

Thankyou very much for that forumlae, it has saved me alot of time already!

**shg** · 03-31-2008, 08:08 PM

Confirmed with Ctrl+Shift+Enter instead on Enter, and entered simutaneously in enough cells to show all the stats you want to see.

**rambochuck** · 04-01-2008, 08:20 AM

Thanks.

One more question - how can I make it so the forumla ignores empty cells? Or so the formula automatically includes data to the 'end' of the column - as my data column lengths differ.

Edit: I've managed to use COUNTIF to return the total number of rows in a column, but how can I reference to this number in the LINEST forumla? For example if I have 400 rows, and the COUNTIF formula is in cell A1, I would want =LINEST(B1:B*(A1), C1:C*(A1), FALSE) but obviously this does not work.

Thanks