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.