Products

GROWTH function

PRO

The GROWTH function in Jspreadsheet Formulas Pro is a tool used to generate a series of y-values based on existing data points. This is done by creating an exponential curve that fits the input data as accurately as possible. This function is particularly useful when trying to predict future values based on the current growth trend in your data. Overall, GROWTH can be a powerful feature for conducting trend analysis and forecasting in Jspreadsheet.

Documentation

Returns an array of y-values generated by evaluating an exponential curve that fits the data points.

Category

Statistical

Syntax

GROWTH(known_y_values,[known_x_values],[new_x's],[const])

Parameter Description
known_y_values The set of y-values that you already know in the relationship y = b*m^x.
known_x_values Optional. The set of x-values that you already know in the relationship y = b*m^x.
new_x's Optional. The new x-values for which you want to generate corresponding y-values using the exponential curve.
const Optional. A logical value that specifies whether to force the constant b to equal 1. If const is TRUE, b is set equal to 1; if it is FALSE or omitted, b is calculated normally.

Behavior

The GROWTH function is used to calculate predicted exponential growth by using existing data. Here's how it handles different types of data:

  • Empty Cells: The GROWTH function ignores empty cells in the given range.
  • Text: If the range includes cells containing text, the function will return an error.
  • Booleans: The GROWTH function treats boolean values as numbers: TRUE as 1 and FALSE as 0.
  • Errors: If any cell in the input range contains an error, the GROWTH function will also return an error.
  • Non-Numeric Values: GROWTH function only works with numeric values. Non-numeric values in the range will cause the function to return an error.

Common Errors

Error Description
#VALUE! This error is returned if the input ranges (known_y_values, known_x_values, new_x's) are not numeric or if they contain text, boolean values, or errors.
#REF! This error is returned if the input cell reference is invalid.
#N/A This error is returned if the known_y_values and known_x_values arrays have different lengths.
#NUM! This error is returned if the function fails to converge to a result, which may occur if the data does not fit an exponential model well.

Best practices

  • Always ensure that your known_y_values and known_x_values are numeric and they have the same length to avoid errors.
  • GROWTH function is sensitive to outliers. So, make sure your data does not contain outliers which may distort the result.
  • The GROWTH function is an array function. For it to work properly, you need to enter it as an array formula if you're working on multiple cells.
  • It is recommended to use GROWTH function on data that shows a clear trend of exponential growth. Using it on data that does not follow this trend might give inaccurate or misleading results.

Usage

A few examples using the GROWTH function.

GROWTH(A1:A6,B1:B6,["10","20"])  
GROWTH(A1:A6,,B1:B3)  
GROWTH(A1:A6,B1:B6)  

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

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Month",
        "Sales",
        "Projected Sales"
    ],
    [
        1,
        1000,
        "=GROWTH(B2:B6,A2:A6,A7:A8)"
    ],
    [
        2,
        1200
    ],
    [
        3,
        1500
    ],
    [
        4,
        1800
    ],
    [
        5,
        2200
    ],
    [
        6
    ],
    [
        7
    ]
]
  }]
});
</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('YTc3MjJkNTYzYmRhODU5YTdlY2Q2NDk1Yjg5MTBhNTIzNTY2YzQyYTdlZDBkM2U1MmZmYjRmMzdiZDcwNWI0YjgyZmE4ZGVkMDg2MzRiOGIyZTdlNTAwOWZjMGFlNzBmNWJlMjAzMzY2ZTQ2ZGY0MTI3MzA3MzBlYjdmNjJhMGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pORFF4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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,
        "=GROWTH(B2:B6,A2:A6,A7:A8)"
    ],
    [
        2,
        1200
    ],
    [
        3,
        1500
    ],
    [
        4,
        1800
    ],
    [
        5,
        2200
    ],
    [
        6
    ],
    [
        7
    ]
];

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

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Month",
        "Sales",
        "Projected Sales"
    ],
    [
        1,
        1000,
        "=GROWTH(B2:B6,A2:A6,A7:A8)"
    ],
    [
        2,
        1200
    ],
    [
        3,
        1500
    ],
    [
        4,
        1800
    ],
    [
        5,
        2200
    ],
    [
        6
    ],
    [
        7
    ]
]

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

// 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,
        "=GROWTH(B2:B6,A2:A6,A7:A8)"
    ],
    [
        2,
        1200
    ],
    [
        3,
        1500
    ],
    [
        4,
        1800
    ],
    [
        5,
        2200
    ],
    [
        6
    ],
    [
        7
    ]
]
            }]
        });
    }
}