DocumentationFormulasFunctionsVarp

VARP function

PRO BASIC

The VARP function in Jspreadsheet Formulas Pro calculates the variance of an entire population, the average of the squared deviations from the mean. Use it when the data covers every member of the group being studied; use VAR.S when the data is a sample. It is the compatibility version of VAR.P and returns the same values.

Documentation

Calculates the variance based on an entire population.

Category

Statistical

Syntax

VARP(number1, [number2], ...)

Parameter Description
number1 The first number, cell or range of the population.
[number2], ... Optional. Additional numbers, cells or ranges.

Behavior

The VARP function divides the sum of squared deviations by the number of values, which is why it is smaller than VAR.S for the same data.

  • Text and logical values inside ranges are ignored.
  • Text passed directly as an argument returns #VALUE!, while a logical value passed directly is counted as 1 or 0.
  • A single value returns 0.
  • The square root of VARP is STDEV.P.

Common Errors

Error Description
#VALUE! Returned when an argument is text that cannot be interpreted as a number.

Best practices

  • Keep the range free of numbers stored as text, because they are ignored silently.
  • Do not include headers or totals in the range.
  • Guard with IF(COUNT(range) > 1, VARP(range), "") when the range may hold a single number, since a single value returns 0.
  • Use absolute references for the range when the formula is copied to other cells.

Usage

A few examples using the VARP function.

VARP(1, 2, 3, 4, 5)
// Returns 2

VAR.S(1, 2, 3, 4, 5)
// Returns 2.5, the sample version for comparison

VARP(A2:A6, B2:B6)
// Returns the variance of both ranges together

VARP(5)
// Returns 0

Interactive Spreadsheet Demo

<html>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<script src="https://cdn.jsdelivr.net/npm/jspreadsheet@13/dist/index.min.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet@13/dist/jspreadsheet.min.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>
// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('NWI3YmZmZDNjZjc4YzZhMDI2ZGM3NzM0YjJlZjU1MjUwZDZlMGEwYTkwNjU4MDFkMzkzZDJiM2E3MmZiZmM1NTA5ZTQ5YWJlZTU4Y2M1Yzg1MDRhMGFlNGZiN2NmYjQ1MzZiYzZjMGVlY2M3NjJiN2Q0NjI0OWNlMjkyZGE1ZjIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE9EVXpNVEk1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Sample A",
        "Sample B",
        "",
        "Formula",
        "Result"
    ],
    [
        2,
        1,
        "",
        "VARP A",
        "=VARP(A2:A6)"
    ],
    [
        4,
        3,
        "",
        "VARP B",
        "=VARP(B2:B6)"
    ],
    [
        6,
        5,
        "",
        "VAR.S A",
        "=VAR.S(A2:A6)"
    ],
    [
        8,
        7,
        "",
        "VARP both",
        "=VARP(A2:A6, B2:B6)"
    ],
    [
        10,
        11
    ]
]
  }]
});
</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('NWI3YmZmZDNjZjc4YzZhMDI2ZGM3NzM0YjJlZjU1MjUwZDZlMGEwYTkwNjU4MDFkMzkzZDJiM2E3MmZiZmM1NTA5ZTQ5YWJlZTU4Y2M1Yzg1MDRhMGFlNGZiN2NmYjQ1MzZiYzZjMGVlY2M3NjJiN2Q0NjI0OWNlMjkyZGE1ZjIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE9EVXpNVEk1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

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

    // Worksheet data
    const data = [
    [
        "Sample A",
        "Sample B",
        "",
        "Formula",
        "Result"
    ],
    [
        2,
        1,
        "",
        "VARP A",
        "=VARP(A2:A6)"
    ],
    [
        4,
        3,
        "",
        "VARP B",
        "=VARP(B2:B6)"
    ],
    [
        6,
        5,
        "",
        "VAR.S A",
        "=VAR.S(A2:A6)"
    ],
    [
        8,
        7,
        "",
        "VARP both",
        "=VARP(A2:A6, B2:B6)"
    ],
    [
        10,
        11
    ]
];

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

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Sample A",
        "Sample B",
        "",
        "Formula",
        "Result"
    ],
    [
        2,
        1,
        "",
        "VARP A",
        "=VARP(A2:A6)"
    ],
    [
        4,
        3,
        "",
        "VARP B",
        "=VARP(B2:B6)"
    ],
    [
        6,
        5,
        "",
        "VAR.S A",
        "=VAR.S(A2:A6)"
    ],
    [
        8,
        7,
        "",
        "VARP both",
        "=VARP(A2:A6, B2:B6)"
    ],
    [
        10,
        11
    ]
]

        return {
            data
        };
    }
}
</script>

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

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('NWI3YmZmZDNjZjc4YzZhMDI2ZGM3NzM0YjJlZjU1MjUwZDZlMGEwYTkwNjU4MDFkMzkzZDJiM2E3MmZiZmM1NTA5ZTQ5YWJlZTU4Y2M1Yzg1MDRhMGFlNGZiN2NmYjQ1MzZiYzZjMGVlY2M3NjJiN2Q0NjI0OWNlMjkyZGE1ZjIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE9EVXpNVEk1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// 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: [
    [
        "Sample A",
        "Sample B",
        "",
        "Formula",
        "Result"
    ],
    [
        2,
        1,
        "",
        "VARP A",
        "=VARP(A2:A6)"
    ],
    [
        4,
        3,
        "",
        "VARP B",
        "=VARP(B2:B6)"
    ],
    [
        6,
        5,
        "",
        "VAR.S A",
        "=VAR.S(A2:A6)"
    ],
    [
        8,
        7,
        "",
        "VARP both",
        "=VARP(A2:A6, B2:B6)"
    ],
    [
        10,
        11
    ]
]
            }]
        });
    }
}