VARA function
PRO
BASIC
The VARA
function in Jspreadsheet Formulas Pro is a tool that estimates the variance in a sample of data. This data can include numbers, text, and logical values, making it versatile for different types of data analysis. Essentially, it measures how much the data points in your sample differ from the average value of the sample. It's a useful tool in statistical analysis when you need to understand the spread or variability of your data.
Documentation
Estimates the variance based on a sample, including numbers, text, and logical values.
Category
Statistical
Syntax
VARA(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] |
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, text, or logical values (TRUE or FALSE). |
Behavior
The VARA
function is used to estimate the variance of a sample, including text and logical values. It calculates and returns the variance based on a sample.
- If the data set includes logical values or text representations of numbers,
VARA
includes them in the calculation whereas the regularVAR
function would ignore them. - If the data set includes text that cannot be translated into numbers,
VARA
interprets them as zeroes. - If the data set includes logical values,
VARA
translatesTRUE
as 1 andFALSE
as 0. - If a cell in the reference or range is an empty cell,
VARA
ignores it. - If the data set includes error values,
VARA
returns an error.
Common Errors
Error | Description |
---|---|
#DIV/0! | This error occurs when the data set only contains one number or an empty cell. Variance requires at least two numbers to be calculated. |
#VALUE! | This error occurs when the function is used incorrectly. For example, including a string of text as an argument that doesn't represent a logical value or number. |
#N/A | This error occurs when some cells in the range are not available or do not exist. |
Best practices
- Always ensure the data set contains at least two numbers to avoid the #DIV/0! error.
- Be aware that
VARA
interprets text and logical values differently than other statistical functions. It might be more appropriate to useVAR
if your data set includes these types of values and you want to ignore them.- Keep in mind that
VARA
treats logical values and number-stored-as-text differently thanVAR
. Make sure you are using the function that best suits your data.
Usage
A few examples using the VARA function.
VARA(1, 2, 3, 4, 5)
// returns variance of the numbers 1–5
VARA(A1:A10)
// returns variance of values in A1:A10, including text ("5") and logicals (TRUE/FALSE)
VARA(B1:B10, C1:C10)
// returns variance of combined values across both ranges
VARA(TRUE, FALSE, 1, "2")
// treats TRUE as 1, FALSE as 0, "2" as 2 → calculates variance of [1,0,1,2]
VARA("hello", 10, 20)
// "hello" is non-numeric text → treated as 0, calculates variance of [0,10,20]
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('N2QxNjZkZWFkZmIwYTBjMjg1ZjQ1NTU4MWI4ZjdhOTdmYjk0YTE3Y2IxMjE5NmNkMWI2NzRiNDU2MWY3YmEwMDU0ZWMzNDJlNzI4NTc0MWJkYmFhYjI5Yzk3YTEwNmY0MjQ2MDI2MzZkMTI3MzE4MWM4MjAyMDE1NzY5MjdiYjIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNekkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Survey Response",
"Rating",
"Comments"
],
[
85,
"Good",
"TRUE"
],
[
92,
"Excellent",
"FALSE"
],
[
"N/A",
78,
"TRUE"
],
[
88,
"Fair",
"FALSE"
],
[
"=VARA(A2: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('N2QxNjZkZWFkZmIwYTBjMjg1ZjQ1NTU4MWI4ZjdhOTdmYjk0YTE3Y2IxMjE5NmNkMWI2NzRiNDU2MWY3YmEwMDU0ZWMzNDJlNzI4NTc0MWJkYmFhYjI5Yzk3YTEwNmY0MjQ2MDI2MzZkMTI3MzE4MWM4MjAyMDE1NzY5MjdiYjIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNekkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Survey Response",
"Rating",
"Comments"
],
[
85,
"Good",
"TRUE"
],
[
92,
"Excellent",
"FALSE"
],
[
"N/A",
78,
"TRUE"
],
[
88,
"Fair",
"FALSE"
],
[
"=VARA(A2: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('N2QxNjZkZWFkZmIwYTBjMjg1ZjQ1NTU4MWI4ZjdhOTdmYjk0YTE3Y2IxMjE5NmNkMWI2NzRiNDU2MWY3YmEwMDU0ZWMzNDJlNzI4NTc0MWJkYmFhYjI5Yzk3YTEwNmY0MjQ2MDI2MzZkMTI3MzE4MWM4MjAyMDE1NzY5MjdiYjIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNekkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Survey Response",
"Rating",
"Comments"
],
[
85,
"Good",
"TRUE"
],
[
92,
"Excellent",
"FALSE"
],
[
"N/A",
78,
"TRUE"
],
[
88,
"Fair",
"FALSE"
],
[
"=VARA(A2: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('N2QxNjZkZWFkZmIwYTBjMjg1ZjQ1NTU4MWI4ZjdhOTdmYjk0YTE3Y2IxMjE5NmNkMWI2NzRiNDU2MWY3YmEwMDU0ZWMzNDJlNzI4NTc0MWJkYmFhYjI5Yzk3YTEwNmY0MjQ2MDI2MzZkMTI3MzE4MWM4MjAyMDE1NzY5MjdiYjIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNekkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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: [
[
"Survey Response",
"Rating",
"Comments"
],
[
85,
"Good",
"TRUE"
],
[
92,
"Excellent",
"FALSE"
],
[
"N/A",
78,
"TRUE"
],
[
88,
"Fair",
"FALSE"
],
[
"=VARA(A2:C5)",
"",
""
]
]
}]
});
}
}