VARPA function

PRO BASIC

The VARPA function in Jspreadsheet Formulas Pro is a tool that helps you calculate the variance across a whole set of data, which could be a complete population. This function considers all data types, including numbers and logical values (like true or false). The variance gives you an understanding of how much your data varies or deviates from the average, helping you make more accurate predictions or decisions based on that data.

Documentation

Calculates the variance based on an entire population, including numbers, and logical values.

Syntax

VARPA(value1, [value2], ...)

Parameter	Description
`value1`	The first value or range of values that you want to calculate the variance of. At least one value is required.
`valueN`	Optional. Additional values or ranges of values that you want to include in the calculation. You can include up to 255 values or ranges. The values can be numbers, or logical values (TRUE or FALSE).

Behavior

The VARPA function is used to calculate the variance of a population, taking into consideration text and boolean values as well. The function calculates the variance based on the entire population and assumes that the provided data is the entire population.

If the dataset contains boolean values, VARPA will treat TRUE as 1 and FALSE as 0.
If the dataset contains text representations of numbers, VARPA will interpret them as numbers and include them in the calculation.
If the dataset contains text that cannot be interpreted as numbers, VARPA will treat them as zeros.
Empty cells within the dataset are ignored by the VARPA function.
If the function encounters an error in a cell, it will return that error.

Common Errors

Error	Description
#DIV/0!	This error occurs when no valid numeric or boolean values are provided to the function.
#VALUE!	This error occurs when the function encounters a cell with a non-numeric value that cannot be interpreted as a number or boolean.
#N/A	This error occurs when the function encounters a cell with the `#N/A` error.

Best practices

Ensure that your data is indeed the entire population before using the VARPA function. If your data is a sample of the population, consider using the VAR.S function instead.

Keep in mind that VARPA function treats text representations of numbers and booleans as numbers. If this is not your intended behavior, you may need to clean up your data before applying VARPA.

Remember that the VARPA function ignores empty cells. If you want to treat empty cells as zeros, you will need to fill them in before executing the function.

Check your data for errors before applying the VARPA function, as encountering an error will cause the function to return that error.

Usage

A few examples using the VARPA function.

VARPA(1, 2, 3, 4)              // Variance of numeric values
VARPA(10, TRUE, FALSE)         // TRUE=1, FALSE=0 → variance with logical values
VARPA(A1:A10)                  // Variance of values in a single range
VARPA(B1:B5, C1:C5)            // Variance across multiple ranges
VARPA("5", "7", TRUE, FALSE)   // Text parsed as numbers + boolean values

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

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Student",
        "Test Score",
        "Pass/Fail"
    ],
    [
        "Alice",
        85,
        true
    ],
    [
        "Bob",
        72,
        false
    ],
    [
        "Carol",
        91,
        true
    ],
    [
        "David",
        68,
        false
    ],
    [
        "Population Variance:",
        "=VARPA(B2:B5)",
        "=VARPA(C2:C5)"
    ]
]
  }]
});
</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('ZTNjYzhjNGU3N2NmZDA5NTU1NzFhMGI0ZWRiMjVmNTliN2MyNzUzYTQ4N2JlNTUxYzExMWU2ZTgyOTYwZTkwMmY1YzA1MjgxMDBlNmM4NTc2MzIxYTFjNmNmYTk0OGFkMWMxMmEwMTk1MTdlNWY4N2U2M2U3Y2Y2M2UwM2ZiZTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNalV4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Student",
        "Test Score",
        "Pass/Fail"
    ],
    [
        "Alice",
        85,
        true
    ],
    [
        "Bob",
        72,
        false
    ],
    [
        "Carol",
        91,
        true
    ],
    [
        "David",
        68,
        false
    ],
    [
        "Population Variance:",
        "=VARPA(B2:B5)",
        "=VARPA(C2:C5)"
    ]
];

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

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Student",
        "Test Score",
        "Pass/Fail"
    ],
    [
        "Alice",
        85,
        true
    ],
    [
        "Bob",
        72,
        false
    ],
    [
        "Carol",
        91,
        true
    ],
    [
        "David",
        68,
        false
    ],
    [
        "Population Variance:",
        "=VARPA(B2:B5)",
        "=VARPA(C2:C5)"
    ]
]

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

// 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: [
    [
        "Student",
        "Test Score",
        "Pass/Fail"
    ],
    [
        "Alice",
        85,
        true
    ],
    [
        "Bob",
        72,
        false
    ],
    [
        "Carol",
        91,
        true
    ],
    [
        "David",
        68,
        false
    ],
    [
        "Population Variance:",
        "=VARPA(B2:B5)",
        "=VARPA(C2:C5)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub