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.

Resources