Calculate slope and intercept value for logarithmic trendline as excel

0 votes

I'm creating logarithmic trendlines with the help of an Excel algorithm. However, I am unable to calculate the slope and intercept values as done in Excel. I believe my formula contained several errors.

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;

Fiddle link

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 derive the Excel formula in JavaScript.

Nov 8, 2022 in Others by Kithuzzz
• 38,020 points
1,132 views

1 answer to this question.

0 votes

You missed Math.log() for ln() in Excel. Edit 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've verified the output is the same as Excel.

> Slope

3.8860409979365333

> Intercept

-5.252238189415747
answered Nov 8, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
0 answers

How is it possible for DynamoDB to support both Key-Value and Document database properties at the same time

As per DynamoDB's documentation, it supports both ...READ MORE

Apr 5, 2022 in Others by Kichu
• 19,050 points
711 views
0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 63,720 points
1,157 views
0 votes
1 answer

Create Excel file and save as PDF.

Office 2013 still has the Interop library and ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 63,720 points
1,308 views
0 votes
1 answer

How do you calculate the Quintile for groups of rows in Excel?

Use this formula: =MAX(1,ROUNDUP(10*PERCENTRANK($C:$C,$C2,4),0)) To divide into whichever many ...READ MORE

answered Oct 17, 2022 in Others by narikkadan
• 63,720 points
1,237 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
928 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,720 points
1,925 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,720 points
1,680 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,720 points
1,230 views
0 votes
1 answer

Can an Excel xll function indicate that the return value should be displayed as a date and not a number?

To my knowledge, the only method to ...READ MORE

answered Jan 5, 2023 in Others by narikkadan
• 63,720 points
877 views
0 votes
1 answer

Thousand and million formatting for negative numbers (excel/ googlesheets)

Its not possible. What you could do is ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 63,720 points
1,231 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP