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