Calculate slope and intercept value for log trendline as excel

I am creating logarithmic trendlines and using an Excel formula for this. But I cannot calculate the slope and intercept value calculated in Excel. I think I made some mistakes in my formula.

This is my code

 var X= [10, 25, 30, 40]; Y= [5, 4, 7, 12];

 var Slope, Intercept, SX = 0, SY = 0,
            SXX = 0, SXY = 0, SYY = 0,
            SumProduct = 0, N = X.length;

            for (var i = 0; i < N; i++) {
                SX = SX + X[i];
                SY = SY + Y[i];
                SXY = SXY + X[i] * Y[i];
                SXX = SXX + X[i] * X[i];
                SYY = SYY + Y[i] * Y[i];
            }

            Slope = ((N * SXY) - (SX * SY)) / ((N * SXX) - (SX * SX));

            Intercept = (SY - (Slope * SX)) / N;

      

Link to script

Excel formula:

Logarithmic equation: y = (c * LN (x)) + b

Where:

c = INDEX(LINEST(y,LN(x)),1)
b = INDEX(LINEST(y,LN(x)),1,2)

      

Screenshot for Excel output

enter image description here

Please suggest how to get Excel formula in JavaScript.

+3


source to share


1 answer


What you missed Math.log()

for ln()

in Excel. Change for

parts like this.

for (var i = 0; i < N; i++) {
    SX = SX + Math.log(X[i]);
    SY = SY + Y[i];
    SXY = SXY + Math.log(X[i]) * Y[i];
    SXX = SXX + Math.log(X[i]) * Math.log(X[i]);
    SYY = SYY + Y[i] * Y[i];
}

      



I checked the output is sams like Excel.

> Slope
3.8860409979365333
> Intercept
-5.252238189415747

      

+4


source







All Articles