FORECAST.LINEAR function

PRO

The FORECAST.LINEAR function in Jspreadsheet Formulas Pro is a useful tool that helps you calculate or predict an upcoming value using the linear regression method on existing datasets. Essentially, it applies linear regression to identify a trend in your existing data and then estimates a future value along that trend. This can be especially useful when you're trying to project future figures for things like sales, costs, or other key metrics in your dataset.

Documentation

Calculates or predicts a future value based on linear regression of a data set.

Syntax

FORECAST.LINEAR(x, known_y_values, known_x_values)

Parameter	Description
`x`	The x-value used to predict the y-value.
`known_y_values`	An array of y-values representing known data points that you want to use in the regression analysis.
`known_x_values`	An array of x-values representing known data points that you want to use in the regression analysis. The number of elements in known_x_values should be equal to the number of elements in known_y_values.

Behavior

The FORECAST.LINEAR function is used to predict a future value along a linear trend. It calculates, or predicts, a future value by using existing values. The existing values are used to calculate a linear regression.

The function requires three arguments: x, known_y_values and known_x_values.
If any cells in known_y_values or known_x_values are empty or contain text or logical values, the FORECAST.LINEAR function will ignore those cells.
The function will return a #DIV/0! error if the variance of known_x_values equals zero.
The function will return a #N/A error if the length of known_x_values is less than the length of known_y_values.
If x is a logical value, the function will consider it as a numerical value.
If known_y_values or known_x_value are text values that cannot be converted to numbers, the function returns a #VALUE! error. Logical values are converted to numbers (TRUE=1, FALSE=0).

Common Errors

Error	Description
#DIV/0!	This error is returned if the variance of known_x_values equals zero.
#N/A	This error is returned if the length of known_x_values have different lengths.
#VALUE!	This error is returned if known_y_values or known_x_values are logical values or text.
#NUM!	This error is returned if the length of known_x_values and known_y_values is less than 1.

Best practices

Always ensure that the known_x_values and known_y_values arrays are of the same length, otherwise, the function will return an error.

The FORECAST.LINEAR function is sensitive to outliers. Therefore, it is crucial to clean your data and remove any possible outliers before using this function.

Avoid using logical values or text within your known_y_values or known_x_values as they will be considered as #VALUE! errors.

Be aware that FORECAST.LINEAR function ignores cells in known_y_values and known_x_values that contain text or logical values. It only considers numeric values for computations.

Usage

A few examples using the FORECAST.LINEAR function.

FORECAST.LINEAR(10, B2:B8, A2:A8)  
Predicts the value corresponding to x=10 using known y-values in B2:B8 and x-values in A2:A8.  

FORECAST.LINEAR(25, Sales, Months)  
Predicts sales for month 25 based on the trend in the named ranges `Sales` and `Months`.  

FORECAST.LINEAR(2025, Revenue, Year)  
Estimates revenue for the year 2025 using historical data from `Revenue` and `Year` columns.

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MGIzZmVhYTE4NzgwMWMyYTM4YzdmMDdmNzdiZTRiNzI4ZGUwOTcxY2FhNWQ0OWFjOTMxZjRiYzM1YWZkYjc4MmUzNGJmNzUzNzc2NjJlZTAyNTRkZTg5ZjdhZDIzMGQxMjM3ZmRmOTM3MjI3NGZkYzI3NjQ4MDcwNzMzMGFhMzgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3lPVEEyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Month",
        "Sales",
        "Forecast for Month 7"
    ],
    [
        1,
        100
    ],
    [
        2,
        150
    ],
    [
        3,
        200
    ],
    [
        4,
        250
    ],
    [
        5,
        300
    ],
    [
        6,
        350
    ],
    [
        7,
        "=FORECAST.LINEAR(7,B2:B7,A2:A7)"
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('MGIzZmVhYTE4NzgwMWMyYTM4YzdmMDdmNzdiZTRiNzI4ZGUwOTcxY2FhNWQ0OWFjOTMxZjRiYzM1YWZkYjc4MmUzNGJmNzUzNzc2NjJlZTAyNTRkZTg5ZjdhZDIzMGQxMjM3ZmRmOTM3MjI3NGZkYzI3NjQ4MDcwNzMzMGFhMzgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3lPVEEyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Month",
        "Sales",
        "Forecast for Month 7"
    ],
    [
        1,
        100
    ],
    [
        2,
        150
    ],
    [
        3,
        200
    ],
    [
        4,
        250
    ],
    [
        5,
        300
    ],
    [
        6,
        350
    ],
    [
        7,
        "=FORECAST.LINEAR(7,B2:B7,A2:A7)"
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('MGIzZmVhYTE4NzgwMWMyYTM4YzdmMDdmNzdiZTRiNzI4ZGUwOTcxY2FhNWQ0OWFjOTMxZjRiYzM1YWZkYjc4MmUzNGJmNzUzNzc2NjJlZTAyNTRkZTg5ZjdhZDIzMGQxMjM3ZmRmOTM3MjI3NGZkYzI3NjQ4MDcwNzMzMGFhMzgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3lPVEEyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Month",
        "Sales",
        "Forecast for Month 7"
    ],
    [
        1,
        100
    ],
    [
        2,
        150
    ],
    [
        3,
        200
    ],
    [
        4,
        250
    ],
    [
        5,
        300
    ],
    [
        6,
        350
    ],
    [
        7,
        "=FORECAST.LINEAR(7,B2:B7,A2:A7)"
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MGIzZmVhYTE4NzgwMWMyYTM4YzdmMDdmNzdiZTRiNzI4ZGUwOTcxY2FhNWQ0OWFjOTMxZjRiYzM1YWZkYjc4MmUzNGJmNzUzNzc2NjJlZTAyNTRkZTg5ZjdhZDIzMGQxMjM3ZmRmOTM3MjI3NGZkYzI3NjQ4MDcwNzMzMGFhMzgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3lPVEEyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "Month",
        "Sales",
        "Forecast for Month 7"
    ],
    [
        1,
        100
    ],
    [
        2,
        150
    ],
    [
        3,
        200
    ],
    [
        4,
        250
    ],
    [
        5,
        300
    ],
    [
        6,
        350
    ],
    [
        7,
        "=FORECAST.LINEAR(7,B2:B7,A2:A7)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub