Forum Discussion
Couldn't calculate exponential trendline intercept value using exponential equation.
HansVogelaar., 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.
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:
JoeUser2004 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.
JoeUser2004 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.
(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.
