Measuring how far a data point is from an exponential trendline

[flyjetz]

(First time poster) I have a set of exponential data (over 23,000 data points) that is represented by an exponential trendline with a formula of y=6.0711e^(0.0003x). I have attempted to create a formula to derive the expected value by using =6.07111*EXP(0.0003*x), but the values at the high end of the data seem way too big. My guess is that the 0.0003 is rounded too much and I can't derive a useful formula at the extreme right end of the data. What I'm trying to do is determine, at each point, how far away (on a percentage basis) the data point is from the trendline. Can anyone help me solve this. Thank you!

 

[joeu2004]

I have attempted to create a formula to derive the expected value by using =6.07111*EXP(0.0003*x), but the values at the high end of the data seem way too big. My guess is that the 0.0003 is rounded too much

Probably right. So let's solve that problem first. If you are getting the formula from your own chart, you can change the format of the trendline label to show greater precision.

First, be sure that you are using an XY Scatter chart, not a Line chart. For a Line chart, the trendline is based on x=1,2,3,etc instead of the actual values on the x-axis.

Right-click the trendline label, click Format Trendline Label, Number. On the right, click a Category and enter a number of decimal places.

I prefer Scientific with 14 decimal places, because that displays all coefficients with the greatest precision that Excel formats, regardless of magnitude.

But in your case, Number with 9 decimal places would probably suffice (the smallest coefficient with 6 significant digits).

Now you can copy-and-paste the coefficients into your spreadsheet.

-----

Alternatively, you could use LINEST to calculate the coefficients directly in your spreadsheet. But for exponential trendlines, that requires a little finesse, which might be overwhelming.

With your x-data in X1:X23000 and y-data in Y1:Y23000, select A1:B1 (2 horizontal cells) and array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:

=LINEST(LN(Y1:Y23000), X1:X23000)

Then the formula to estimate Y1 is:

=EXP(B1) * EXP(A1*X1)

-----

What I'm trying to do is determine, at each point, how far away (on a percentage basis) the data point is from the trendline

With the estimated y-values in Z1:Z23000, the percentage difference for Y1 is:

=Z1/Y1 - 1

formatted as Percentage.

However, we usually use the sum of the squared residuals (variously abbreviated SSR, RSS, SSE) or RSQ to measure fit.

 

[joeu2004]

Errata....

Then the formula to estimate Y1 is:
=EXP(B1) * EXP(A1*X1)

To allow for copying down a column, that should be =EXP($B$1) * EXP($A$1*X1).

 

[flyjetz]

Probably right. So let's solve that problem first. If you are getting the formula from your own chart, you can change the format of the trendline label to show greater precision.

First, be sure that you are using an XY Scatter chart, not a Line chart. For a Line chart, the trendline is based on x=1,2,3,etc instead of the actual values on the x-axis.

Right-click the trendline label, click Format Trendline Label, Number. On the right, click a Category and enter a number of decimal places.

I prefer Scientific with 14 decimal places, because that displays all coefficients with the greatest precision that Excel formats, regardless of magnitude.

But in your case, Number with 9 decimal places would probably suffice (the smallest coefficient with 6 significant digits).

Now you can copy-and-paste the coefficients into your spreadsheet.

-----

Alternatively, you could use LINEST to calculate the coefficients directly in your spreadsheet. But for exponential trendlines, that requires a little finesse, which might be overwhelming.

With your x-data in X1:X23000 and y-data in Y1:Y23000, select A1:B1 (2 horizontal cells) and array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:

=LINEST(LN(Y1:Y23000), X1:X23000)

Then the formula to estimate Y1 is:

=EXP(B1) * EXP(A1*X1)

-----



With the estimated y-values in Z1:Z23000, the percentage difference for Y1 is:

=Z1/Y1 - 1

formatted as Percentage.

However, we usually use the sum of the squared residuals (variously abbreviated SSR, RSS, SSE) or RSQ to measure fit.

Changing the number of decimal places on the trendline formula solved my issue. Thank you so much!