Curve fitting/goal seek problem

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