DVARP function
PRO
BASIC
The DVARP
function in Jspreadsheet Formulas Pro is a powerful tool that helps you calculate the variance of a population. In statistical terms, variance refers to a numerical value that describes how much the data points in a population differ from the average value. When used in Jspreadsheet, the DVARP
function analyzes your dataset to provide a clear measure of variability across the entire population. This is particularly useful in statistical reporting and quality control to assess consistency.
Documentation
The DVARP function calculates the variance of a population.
Category
Database
Syntax
DVARP(database, field, criteria)
Parameter | Description |
---|---|
database |
The range of cells that makes up the database. |
field |
The column label that contains the numbers for which you want the variance. |
criteria |
The range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column. |
Behavior
The DVARP
function is used to estimate variance for the entire population based on a selected database. The function takes three arguments: the database, the field (indicating the column in the database to be used), and the criteria (a set of conditions that the cells in the database must meet to be included in the calculation). Here's how it handles different scenarios:
- Empty cells: The
DVARP
function ignores empty cells in the database. - Text: If the field argument is a text value that corresponds to a column name in the database,
DVARP
uses that column for calculation. If the text is not a column name or if text is present in the cells of the selected column,DVARP
returns an error. - Booleans: The
DVARP
function does not support Boolean values. If a Boolean value is encountered in the selected column, the function returns an error. - Errors: If an error is present in the cells of the selected column,
DVARP
also returns an error.
Common Errors
Error | Description |
---|---|
#VALUE! | This error occurs when the field argument doesn't match any column in the database or if the criteria argument is invalid. |
#DIV/0! | This error occurs when there are less than two rows in the database that meet the given criteria. |
#N/A | TThis error occurs when the field argument is not provided, or if the database or field cannot be found. |
Best practices
- Always make sure the field argument correctly matches a column name in the database.
- Use meaningful criteria to ensure relevant rows in the database are included in the calculation.
- Handle error values and text in the data before applying the
DVARP
function to avoid errors.- Remember that
DVARP
is used when you want to consider the entire population. If you want to consider a sample of the population, use theDVAR
function instead.
Usage
A few examples using the DVARP function.
DVARP(A2:C10, 'Sales', A1:C2)
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('N2RiNGFhMmIzZmM5ZTlhZTY2ZDU3YTMzMjFmNDQ2MGM4ZmU2ZGZmNzIxMDg5ZDg5YTc0OWI5ZGIyODIwMjhjNDFkM2UyZjRjYjQ5ZjFlYjMyYTRiNGM1MDBiZTMyMGI1YzNiMDc2ZmNiZTRhNWNmZDM0NDE4NTUwZjJmNWZlZWMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNRFF3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Product",
"Region",
"Sales"
],
[
"Laptop",
"North",
""
],
[
"Laptop",
"North",
1200
],
[
"Laptop",
"South",
1100
],
[
"Laptop",
"North",
1300
],
[
"Tablet",
"North",
800
],
[
"Tablet",
"South",
750
],
[
"",
"",
"=DVARP(A2:C7,\"Sales\",A1:C1)"
]
]
}]
});
</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('N2RiNGFhMmIzZmM5ZTlhZTY2ZDU3YTMzMjFmNDQ2MGM4ZmU2ZGZmNzIxMDg5ZDg5YTc0OWI5ZGIyODIwMjhjNDFkM2UyZjRjYjQ5ZjFlYjMyYTRiNGM1MDBiZTMyMGI1YzNiMDc2ZmNiZTRhNWNmZDM0NDE4NTUwZjJmNWZlZWMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNRFF3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Product",
"Region",
"Sales"
],
[
"Laptop",
"North",
""
],
[
"Laptop",
"North",
1200
],
[
"Laptop",
"South",
1100
],
[
"Laptop",
"North",
1300
],
[
"Tablet",
"North",
800
],
[
"Tablet",
"South",
750
],
[
"",
"",
"=DVARP(A2:C7,\"Sales\",A1:C1)"
]
];
// 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('N2RiNGFhMmIzZmM5ZTlhZTY2ZDU3YTMzMjFmNDQ2MGM4ZmU2ZGZmNzIxMDg5ZDg5YTc0OWI5ZGIyODIwMjhjNDFkM2UyZjRjYjQ5ZjFlYjMyYTRiNGM1MDBiZTMyMGI1YzNiMDc2ZmNiZTRhNWNmZDM0NDE4NTUwZjJmNWZlZWMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNRFF3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Product",
"Region",
"Sales"
],
[
"Laptop",
"North",
""
],
[
"Laptop",
"North",
1200
],
[
"Laptop",
"South",
1100
],
[
"Laptop",
"North",
1300
],
[
"Tablet",
"North",
800
],
[
"Tablet",
"South",
750
],
[
"",
"",
"=DVARP(A2:C7,\"Sales\",A1:C1)"
]
]
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('N2RiNGFhMmIzZmM5ZTlhZTY2ZDU3YTMzMjFmNDQ2MGM4ZmU2ZGZmNzIxMDg5ZDg5YTc0OWI5ZGIyODIwMjhjNDFkM2UyZjRjYjQ5ZjFlYjMyYTRiNGM1MDBiZTMyMGI1YzNiMDc2ZmNiZTRhNWNmZDM0NDE4NTUwZjJmNWZlZWMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNRFF3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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: [
[
"Product",
"Region",
"Sales"
],
[
"Laptop",
"North",
""
],
[
"Laptop",
"North",
1200
],
[
"Laptop",
"South",
1100
],
[
"Laptop",
"North",
1300
],
[
"Tablet",
"North",
800
],
[
"Tablet",
"South",
750
],
[
"",
"",
"=DVARP(A2:C7,\"Sales\",A1:C1)"
]
]
}]
});
}
}