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;
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
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 to share