TREND function

PRO

The TREND function in Jspreadsheet Formulas Pro is a useful tool for predicting future values based on existing data points. It operates by identifying a linear trend in your current information and returns estimated values along this trend line. This can be particularly helpful in forecasting sales, revenue, or other trends in your data. It's a great way to make educated guesses about future data points based on what's happened in the past.

Documentation

Returns values along a linear trend.

Syntax

TREND(known_y's, [known_x's], [new_x's], [const])

Parameter	Description
`known_y's`	An array or range of dependent data points
`[known_x's]`	Optional. Array or range of independent data points. If omitted, the array [1,2,3,...] is used.
`[new_x's]`	Optional. Array or range of new x-values for which you want to predict corresponding y-values. If omitted, the same x-values as [known_x's] are used.
`[const]`	Optional. Logical value specifying whether to force the constant b to equal 0. If omitted or FALSE, b is calculated normally. If TRUE, b is set equal to 0.

Behavior

The TREND function is used for predicting a linear trend based on the known x-values and y-values by using the least squares method. The basic behavior of this function is as follows:

It accepts both arrays and ranges of cells for x-values and y-values.
It can handle empty cells, but those will be ignored in the calculation.
Text or boolean values in the range of cells will result in an error.
If the x-values and y-values have different lengths, the function will return an error.
If there is an error in any cell in the range, the function will return an error.
The function will return an array of predicted y-values if the new_x's are provided, otherwise, it will return a single predicted y-value for a new_x of 1.

Common Errors

Error	Description
#VALUE!	This error occurs if the given argument is non-numeric, a boolean, or text.
#N/A	This error occurs if the lengths of the known y-values and known x-values are not equal.
#REF!	This error occurs if the given cell reference is not valid.
#DIV/0!	This error occurs if the function is trying to divide by zero, i.e., known x-values are all the same.

Best practices

Always ensure that the known y-values and known x-values have the same length to avoid the #N/A error.

Avoid using text, boolean values, or non-numeric values in the cells for this function to prevent the #VALUE! error.

Check the cell references before using them in the function to avoid the #REF! error.

Use the TREND function to predict future values but keep in mind that it assumes a linear relationship between x-values and y-values. If the relationship is not linear, the predictions may not be accurate.

Usage

A few examples using the TREND function.

Example 1:  
TREND([2,4,6,8],[1,2,3,4])  
→ Returns [2,4,6,8] because the relationship is perfectly linear (y = 2x).  

Example 2:  
TREND([2,4,6,8],[1,2,3,4],[5,6])  
→ Returns [10,12], predicting future values for x=5 and x=6.  

Example 3:  
TREND([3,7,11],[1,2,3],[4],TRUE)  
→ Returns [15], predicting the next value in the series (linear growth y = 4x - 1).

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('NjJlZjE0YTMzODMzYTJlMDEwNDc5NmVlZDJhYTA1MzgxMzc2ZDdkZDBiNmYyZGUwN2QwNGIwMDkzNTAxY2UxNDJhYmVmOWI2NGNiMzExODJiMDM0NjMxMjA4NzNlMGY4MzFiNjJjODU4NjcxYTEyNjU1MjJkNjIwMmU1MjNiMGMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOekExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Month",
        "Sales",
        "Projected Sales"
    ],
    [
        1,
        1000,
        "=TREND(B2:B6,A2:A6,A2:A6)"
    ],
    [
        2,
        1200
    ],
    [
        3,
        1350
    ],
    [
        4,
        1600
    ],
    [
        5,
        1750
    ]
]
  }]
});
</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('NjJlZjE0YTMzODMzYTJlMDEwNDc5NmVlZDJhYTA1MzgxMzc2ZDdkZDBiNmYyZGUwN2QwNGIwMDkzNTAxY2UxNDJhYmVmOWI2NGNiMzExODJiMDM0NjMxMjA4NzNlMGY4MzFiNjJjODU4NjcxYTEyNjU1MjJkNjIwMmU1MjNiMGMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOekExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Month",
        "Sales",
        "Projected Sales"
    ],
    [
        1,
        1000,
        "=TREND(B2:B6,A2:A6,A2:A6)"
    ],
    [
        2,
        1200
    ],
    [
        3,
        1350
    ],
    [
        4,
        1600
    ],
    [
        5,
        1750
    ]
];

    // 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('NjJlZjE0YTMzODMzYTJlMDEwNDc5NmVlZDJhYTA1MzgxMzc2ZDdkZDBiNmYyZGUwN2QwNGIwMDkzNTAxY2UxNDJhYmVmOWI2NGNiMzExODJiMDM0NjMxMjA4NzNlMGY4MzFiNjJjODU4NjcxYTEyNjU1MjJkNjIwMmU1MjNiMGMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOekExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Month",
        "Sales",
        "Projected Sales"
    ],
    [
        1,
        1000,
        "=TREND(B2:B6,A2:A6,A2:A6)"
    ],
    [
        2,
        1200
    ],
    [
        3,
        1350
    ],
    [
        4,
        1600
    ],
    [
        5,
        1750
    ]
]

        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('NjJlZjE0YTMzODMzYTJlMDEwNDc5NmVlZDJhYTA1MzgxMzc2ZDdkZDBiNmYyZGUwN2QwNGIwMDkzNTAxY2UxNDJhYmVmOWI2NGNiMzExODJiMDM0NjMxMjA4NzNlMGY4MzFiNjJjODU4NjcxYTEyNjU1MjJkNjIwMmU1MjNiMGMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOekExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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",
        "Projected Sales"
    ],
    [
        1,
        1000,
        "=TREND(B2:B6,A2:A6,A2:A6)"
    ],
    [
        2,
        1200
    ],
    [
        3,
        1350
    ],
    [
        4,
        1600
    ],
    [
        5,
        1750
    ]
]
            }]
        });
    }
}

Edit this page on GitHub