How to Convert an Exponential Trendline Equation to a Formula

[testeranalyzer]

My data is in 2 columns. Column A is my Y axis, Column B is my X axis.

I have maybe 30 rows of data, and I created a line with markers graph using that data.

Then I right clicked on the data line to have Excel create a Trendline using Exponential.

I then clicked the option to show the equation for the trendline.

Here is the equation: y = 1939.2e-0.082x

I'd like to enter a formula in Excel, so that for a known X value, I can get the corresponding Y value on the trendline.

Thanks!

 

[william_man]

Use the natural logarithm function (LN) to inverse an exponential trendline.
This should return the X value, where A2 is your given Y value:

=LN(A2/1939.2)/0.082

 

[testeranalyzer]

Thank you for helping out!

I am not having any luck getting that formula to return an expected value.

What I have is known X values, and I would like a formula based on the exponential trendline to know the Y value.

Here is another example using my most recent data: y = 1836.2e-0.087x

When i use the formula you provided, where B4 is my known X value, the result in Excel is 10.189... but I expected to see something much higher (about 1,400) based on the trendline.

=LN(B4/1836.2)/0.087

I am wondering if I am interpreting the equation correctly.

 

[william_man]

Sorry I think I misread your original post!

My original equation is for converting a known Y value to X.

The equation for converting a known X value to Y would be as follows, where B2 is the X value:

=10.083*EXP(0.0376*B2)

Let me know if that works for you!

 

[shg]

If you are fitting an exponential trendline on a line graph, the x values are the categorical series {1,2,3,...}

 

[joeu2004]

First, be sure that you are using an XY Scatter chart (perhaps with line subtype), not a Line chart. The trendline of a Line chart can be misleading because it always uses 1, 2, 3,... for the x-axis, not the actual x-axis values.

There are two ways to use the trendline formula. The second method, using LINEST, is usually better. But the first method might be easier to understand.

-----

Ostensibly, you might enter formulas of the form =1939.2*EXP(-0.082*A2) starting in C2. Thus, column C contains the trendline estimated y-values.

However, that is not likely to reproduce good results because the coefficients 1939.2 and 0.082 do not have sufficient precision.

Format the trendline label so that the coefficients have more precision. I like the Scientific format with 14 decimal places because that displays all values with the most precision that Excel formats, regardless of their magnitude.

Then I would copy the coefficients into cells; for example, -0.082 into X1 and 1939.2 into Y1. Then enter the following formula into C2 and copy down:

=$Y$1*EXP($X$1*B2)

-----

Usually, it is better to use LINEST.

Select X1:Y1 and array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:

=LINEST(LN(B2:B20), A2:A20)

Then enter the following formula into C2 and copy down:

=EXP($Y$1) * EXP($X$1*A2)

Caveat: LINEST might not work for some (extreme) data, but the chart trendline might work. In that case, resort to using the coefficients in the trendline label.

 

[testeranalyzer]

Thanks for the advice.

The end result I need to get to, is to have one formula that allows me to enter the Y value and get the corresponding Y value on the exponential trendline.

I'm still having a hard time figuring that out.

I tried =LN(A2/1939.2)/0.082 and it did not get me close to the known Y's I have for a given X.

joeu2004 - I am not sure where I would enter the formula in this step "Select X1:Y1 and array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:"

-----
I will share some sample data so we can all be looking at the same data and it is easy to recreate on your side:
(column A) (column B)
[TABLE="width: 174"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD] Y Values [/TD]
[TD] X Values[/TD]
[/TR]
[TR]
[TD] 1,300[/TD]
[TD] 4,000[/TD]
[/TR]
[TR]
[TD] 1,200[/TD]
[TD] 5,000[/TD]
[/TR]
[TR]
[TD] 1,000[/TD]
[TD] 6,000[/TD]
[/TR]
[TR]
[TD] 900[/TD]
[TD] 7,000[/TD]
[/TR]
[TR]
[TD] 850[/TD]
[TD] 8,000[/TD]
[/TR]
[TR]
[TD] 750[/TD]
[TD] 9,000[/TD]
[/TR]
</tbody>[/TABLE]

I created a line chart with 1 data series: Y values =Sheet1!$A$2:$A$7 and then horizontal axis values = =Sheet1!$B$2:$B$7

Then I right click to add trendline, then choose exponential.

Then when I display equation on chart, it shows: y = 1448.5e-0.111x

What I am hoping to do, is get a formula where I can enter an X value of 10,000 or 11,000 or 59,321 etc...any X value, and get the corresponding Y value based on the trendline.

Maybe it is not possible?

Thanks again.

 

[joeu2004]

Column A is my Y axis, Column B is my X axis.

First, my bad! I mixed up your columns: I thought column A has x-values, and column B has y-values.

That is more common, since the Insert Chart feature works best that way.

joeu2004 - I am not sure where I would enter the formula in this step "Select X1:Y1 and array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:"

Honestly, I don't know how to say that any more clearly. "Select X1:Y1 and array-enter ..." means that the formula goes into X1 and Y1.

I will share some sample data so we can all be looking at the same data
[....]
I created a line chart with 1 data series: Y values =Sheet1!$A$2:$A$7 and then horizontal axis values = =Sheet1!$B$2:$B$7
Then I right click to add trendline, then choose exponential.
Then when I display equation on chart, it shows: y = 1448.5e-0.111x

Yes, it is always best to share some data.

And I see that I'm not the only one who can't follow instructions: two people (shg and me) have told you not to use Line Chart.

The trendline is wrong because the Line Chart trendline uses x-values 1, 2, 3,..., regardless of what you display in the x-axis.

Use a Scatter Chart, instead, optionally choosing the Straight Line subtype if you want it to look like a Line Chart.

I hope the images below clarify things for you.

-----

First, how to select the proper chart:





-----

Second, how to set up the Excel design. To simplify things, I put the coefficients in E2:F2 instead of X1:Y1.





The Line Chart on the left demonstrates the incorrect coefficients.

The Scatter Chart on the right (Straight Line subtype) demonstrates how to display the trendline equation in format Scientific with 14 decimal places (i.e. 15 significant digits, the most that Excel formats).

You can manually copy-and-paste the trendline coefficients (with 15 significant digits) into E2 and F2. But note that in my design, -1.11...E-04 goes into E2, and 2.02...E+02 goes into F2, the opposite of the order that they appear in the trendline equation.

(I did that for consistently with LINEST below.)

Also, you would enter a different formula into C2 than what appears in the image above, and copy it into C3:C7, to wit:

=$F$2 * EXP($E$2*B2)

-----

But I recommend that you use LINEST.

Select E2:F2 (that is where the formula goes), type the formula below, then press ctrl+shift+Enter instead of just Enter to array-enter the formula:

=LINEST(LN(A2:A7), B2:B7)

Note that F2 looks very different than the corresponding coefficient in the trendline equation.

The reason it: it is the (natural) log of the coefficient. The actual coefficient is EXP($F$2), which I show in F3.

Then, enter the following formula into C2, and copy it into C3:C7:

=EXP($F$2) * EXP($E$2*B2)

In the image above, C2:C7 show the resulting estimate-y values. Note that they are close to the values in column A.

-----

What I am hoping to do, is get a formula where I can enter an X value of 10,000 or 11,000 or 59,321 etc...any X value, and get the corresponding Y value based on the trendline.

Enter other values of X (10,000, 11,000, 59,321, etc) into B8, B9, B10, etc, and copy the formula in C2 into C8, C9, C10, etc.

 

[joeu2004]

The Line Chart on the left demonstrates the incorrect coefficients.

The Scatter Chart on the right (Straight Line subtype) demonstrates how to display the trendline equation in format Scientific with 14 decimal places (i.e. 15 significant digits, the most that Excel formats).

I forgot to note that the red line is the trendline in both charts.

Note that it looks almost linear. In fact, the linear trendline has almost the same R^2, a measure of fit. And that would have been my choice, in the first place, just looking at the data; it does not look exponential, at least for the example. Perhaps the larger data is more exponential.

 

[testeranalyzer]

This is great! Thanks for the extremely detailed instructions. I was able to replicate your results.