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...
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.
