Couldn't calculate exponential trendline intercept value using exponential equation.

[gayathrimanick]

With the data below, I am trying to calculate intercept value for exponential trendline with the equation below. But got an error #Num!. Could anyone please help to resolve this.

=EXP(INDEX(LINEST(LN(B68:B72), A68:A72), 1, 2))

1/1/2021	3
1/2/2021	5
1/3/2021	3
1/4/2021	4
1/5/2021	1

Thanks in advance.

[#2]

@gayathrimanick 

The data aren't suitable for an exponential trendline:

LOGEST results in

0.785027

1.8E+308

[#3]

Hi Vogelaar, Thank you for the reply.

For the exponential trendline, intercept value calculated as 2E+308 as shown in below trendline equation. I need intercept equation.

Thanks in advance.

[#4]

@gayathrimanick 

1.8E+308 is the second value returned by the LOGEST formula that I posted in my previous reply. The equation on the chart rounds this to 2E+308.

The number -0.242 in the exponent is the LN of the first value returned by LOGEST:

LN(0.785027) = -0.24204

[#5]

@Hans Vogelaar., Thank you. Could you please share the complete equation for getting this value 1.8E+308 or 2E+308 in excel. I need to convert this equation to equivalent c# code.

Thanks in advance.

[#6]

@gayathrimanick 

I don't know the code behind the LOGEST function.

[#7]

@gayathrimanick

First, it is important to explain that your dates are in the MDY form.

Second, the coefficients of the exponential trendline -- especially "2E+308" -- are misleading, if not bogus.

This is evident when we try to format the trendline label as Scientific with 14 decmal to display more precision.  In Excel 2010, "2E+308" becomes 0.00000000000000E+00 (!).

"2E+308" might be rounded from 1.79769313486232E+308, the largest "normalized" value that Excel can calculate.  But I suspect that the coefficient is an even larger "non-normalized" value.  Excel does display 0.00E+00 when we try to format that value as Scientific.

(Aside.... Usually, Excel does not produce and cannot work with "non-normalized" values.  It is a defect if the trendline algorithm produces them.)

It is risky to use dates as the independent variable for exponential formulas, because they are relatively large numbers.  That is why EXP(INDEX(LINEST...)) returns #NUM.

If we use relative day numbers 1 through 5 instead, the exponential trendline becomes y = 5.839830712142600*e^(-0.242036812865043*x)

Then with the relative day numbers in A1:A5 and the "y" values in B1:B5, the array-entered formula =LINEST(LN(B1:B5),A1:A5) returns m = -0.242036812865043 and b = 1.764701808773170.

And EXP(INDEX(LINEST(LN(B1:B5),A1:A5),1,2)) is 5.839830712142600.

Be that as it may, an exponential trendline is a poor estimator of the data, as evidenced by R² = 0.382228123553204.

[#8]

@gayathrimanick  wrote: ``Could you please share the complete equation for getting`` the trendline formula.

Adapted from the LINEST function support page, excerpted below.

For LINEST(ln(Y), X), ln(y) = m*x + b, and y = EXP(b) * EXP(m*x), where assuming X is A1:A5 and Y is B1:B5:

for m, array-enter:

=SUMPRODUCT(A1:A5 - AVERAGE(A1:A5), LN(B1:B5) - AVERAGE(LN(B1:B5)))

/ SUMPRODUCT((A1:A5 - AVERAGE(A1:A5))^2)

for b, array-enter (replace "m" with a cell reference):

=AVERAGE(LN(B1:B5)) - m*AVERAGE(A1:A5)

-----

LINEST function support page excerpt:

[#9]

@Joe User Thanks for the reply. You are right when using datetime for the exponential trendline, the result of the equation tends to EXP(10698.8236831922). 

Could you please help? Is there any other way to calculate the intercept value for the exponential trendline? When doing this function EXP(10698.8236831922), I am getting infinity as you said the largest value is about EXP(709.782712893384).

I couldn't draw trendline for the exponential type due to getting infinity for intercept field. Please let me know, is there any other way to calculate intercept value.

Thanks in advance.

[#10]

@Joe User Thanks for the reply. 

I used the suggested =AVERAGE(LN(B1:B5)) - m*AVERAGE(A1:A5) for calculating b. When using EXP(b) again I got #Num! error. When evaluating the EXP(b) value, the resultant value will be like 

How do I get rid of this? Is there any other feasible way "2E+308" or "1.79769313486232E+308" to get this value for b.

Thanks in advance.

[#11]

(Resubmitted to improve example, with critical correction.)

@gayathrimanick  wrote: ``I used the suggested =AVERAGE(LN(B1:B5)) - m*AVERAGE(A1:A5) for calculating b. ``

To be clear, that was suggested and necessary only ``to convert this equation to equivalent c# code``, as you requested.

In Excel, I would continue to use INDEX(LINEST(LN(B1:B5),A1:A5),1,2) to calculate "log b" for the linear formula ln(y) = m*x + ln(b).

And I would use EXP(INDEX(LINEST(LN(B1:B5),A1:A5),1,2)) to calculate "b" for the exponential formula y = b*EXP(m*x).

-----

@gayathrimanick  wrote: ``When using EXP(b) again I got #Num! error.``

Apparently, you overlooked the most important part of my response, namely:  use relative day numbers 1 through 5 instead of the actual dates 1/1/2021 through 1/5/2021 (in MDY form).

This is demonstrated in the attached Excel file and depicted below.

(Note that the exponential trendline does not fit the data well.  It is a poor estimator.)

Use B2:B6 and C2:C6 to create the XY Scatter chart.

B2:  =A2-$A$2+1

LINEST formulas:

A9:B9:  =LINEST(LN(C2:C6), B2:B6)

C9:       =EXP(B9)

A12:C15 demonstrate how to use LINEST results to estimate Y on other dates:

B12:  =A12-$A$2+1

C12:  =$C$9 * EXP($A$9*B12)

Note that for the exponential formula y = b*EXP(m*x), "x" is a relative day number, relative to "day one" in A2.