Forum Discussion

gayathrimanick's avatar
gayathrimanick
Copper Contributor
Dec 31, 2021

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...
Charting
excel
gayathrimanick's avatar
gayathrimanick
Copper Contributor
Dec 31, 2021

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.

 

HansVogelaar's avatar
HansVogelaar
MVP
Dec 31, 2021

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

  • gayathrimanick's avatar
    gayathrimanick
    Copper Contributor
    Dec 31, 2021

    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.

    • JoeUser2004's avatar
      JoeUser2004
      Bronze Contributor
      Dec 31, 2021

      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:

       

      • gayathrimanick's avatar
        gayathrimanick
        Copper Contributor
        Jan 02, 2022

        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.

      • gayathrimanick's avatar
        gayathrimanick
        Copper Contributor
        Dec 31, 2021

        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.