Power Law Trendline Apparent Error

[jwcane]

I have a hyperbolic-appearing dataset, and am using Excel to create a power-law trendline.

Excel seems to be generating a power law coefficient that is about 10 times too low. It appears that, in the trendline equation, the coefficient should have six numbers but only five are displayed. If I add a trailing zero to the coefficient, the fit is reasonably good. Otherwise, results are horribly bad.

Is there a simple way to gain access to the equation parameters, other than displaying the equation?

Here are the data:

X Y

2,650 15.1
1,487 38.4
1,043 61.3
714 91.6
538 129.7
469 152.5
415 179.1
384 213.0
354 233.1
337 257.2
300 301.7
268 347.9
254 394.8
236 466.3
230 511.5

[shg]

Welcome to the forum.

Post a workbook and explain in context.

[jwcane]

OK, here is a sample that illustrates my issue. The power-law trendline uses the following relationship to fit the data:

y = 74719 * power( x, -1.36).

which you can see, gives results (yfit) about an order of magnitude too low. Results in the next column, 10*yfit, come in a lot closer to the given data.

It appears that the Excel equation writer may be dropping the last digit of the leading coefficient. Could you show me a way to track the curve-fitting process, and actually see the fitting parameters in worksheet cells.

[martindwilson]

=POWER(10,2) gives 100
=POWER(10,-1) gives 0.1
which is what youd expect,nothing wrong with excel!
even using your data
=74719*A2^-1.36 still gives the same result as
=74719*POWER(A2,-1.36)

[jwcane]

Right, agree with all, but the result is off by x10. Using the formula, 74719*POWER(A2,-1.36), gives results that are too low. 74179 has to be increased to 741790 in order to fit the data. Would like to see the actual coefficient (74179) computed if that is possible, for reasons given in my second posting.

[shg]

Look at the trendline in the graph; the coefficient is 747197, not 74719; there's your order of magnitude.

=LINEST(LN(B2:B16), LN(A2:A16)) returns {-1.36691, 13.52408}, where the first number is the exponent and EXP(13.52...) = 747196.51 is the coefficient.

[MrShorty]

Check for errors or formatting problems in your chart. When I paste your data in, I get a proper fit (with A about 10x the A you are reporting). Seems like an error specific to your spreadsheet rather than a bug in Excel.

As far as extracting coefficients from a chart, I prefer not to. I find it much easier to use LINEST (or LOGEST, if it seems more appropriate) to calculate my coefficients directly in the spreadsheet. I might suggest learning how to use these worksheet functions.

[jwcane]

Problem appears to be with my Macs. One is running Snow Leopard, the other Lion, both variants of OSX. Both of them have Office 2008. Excel on both systems returns the exponent as 74719. Following your replies I loaded the same workbook into Excel 2007 on my laptop running Vista, and the coefficient in the written equation is in fact 747197, confirming your results.

Thanks both for clearing this up for me. Will definitely get on top of LINEST().

[jwcane]

Same behavior with the exponent. Mac gives 1.36, Vista machine gives 1.366,

What is best way to get this in front of the right people at MS?

[jwcane]

re:/ LINEST: When I enter into one cell, LINEST with the arguments that you gave in your reply, Excel returns only the exponent. Any ideas appreciated.

[shg]

It's an array formula.

You need to select TWO cells, e.g., A1 and B1, paste the formula in the formula bar, press and hold the Ctrl & Shift keys, then press Enter.

[jwcane]

Perfect, thank you again. Works fine on my iMac, and avoids any need to display the equation with all of those attendant problems (which are now reduced to the status of a red herring).