VAR Function (OpenOffice Calc)
The VAR function in OpenOffice Calc calculates the sample variance of a dataset. Learn syntax, examples, statistical meaning, common errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the VAR Function Does â–¾
- Calculates sample variance
- Measures how much values deviate from the mean
- Ignores text and empty cells
- Includes dates and times (because they are numeric)
- Works across sheets
- Useful for scientific, financial, and statistical analysis
Use VAR for sample datasets.
Use VARP when working with an entire population.
Use VARP when working with an entire population.
Syntax â–¾
VAR(number1; number2; ...)
Arguments:
- number1, number2, … — Individual values, cell references, or ranges
Statistical Meaning â–¾
VAR uses the sample variance formula:
[ s^2 = \frac{\sum (x_i - \bar{x})^2}{n - 1} ]
Where:
- ( x_i ) = each value
- ( \bar{x} ) = sample mean
- ( n ) = number of values
This formula divides by n − 1, making it appropriate for samples, not full populations.
Basic Examples â–¾
Sample variance of a range
=VAR(A1:A10)
Sample variance of multiple ranges
=VAR(A1:A10; C1:C10)
Sample variance of a list of values
=VAR(10; 25; 7; 99; 3)
Sample variance of dates
If A1:A5 contains dates:
=VAR(A1:A5)
Result: variance in days.
Advanced Examples â–¾
VAR across sheets
=VAR(Sheet1.A1:A100)
VAR with conditions (workaround)
OpenOffice Calc does not have VARIF, but you can use:
=VAR(IF(A1:A100="North"; B1:B100))
Confirm with Ctrl+Shift+Enter.
VAR within a date range
=VAR(IF((A1:A100>=DATE(2025;1;1))*(A1:A100<=DATE(2025;12;31)); B1:B100))
Confirm with Ctrl+Shift+Enter.
VAR excluding zeros
=VAR(IF(A1:A100<>0; A1:A100))
Confirm with Ctrl+Shift+Enter.
VAR of filtered data
VAR does not ignore filtered rows.
Workaround:
=VAR(IF(SUBTOTAL(103; OFFSET(A1; ROW(A1:A100)-ROW(A1); 0)); A1:A100))
Confirm with Ctrl+Shift+Enter.
VAR in a 3D range
=VAR(Sheet1:Sheet5.A1:A10)
Common Errors and Fixes â–¾
VAR returns Err:502 (Invalid argument)
Occurs when:
- Fewer than 2 numeric values exist
- All values are text or empty
- Imported numbers stored as text
Fix: Convert text to numbers:
Data → Text to Columns → OK
VAR returns 0 unexpectedly
Possible causes:
- All values are identical
- Only one numeric value exists
VAR includes values you expected it to ignore
VAR includes:
- Dates
- Times
- Numeric results of formulas
VAR excludes values you expected it to include
VAR ignores:
- Text numbers (
"123") - Empty cells
- Logical values (TRUE/FALSE)
- Errors
Err:508 — Missing parenthesis
Usually caused by:
- Missing
) - Using commas instead of semicolons
Best Practices â–¾
- Use VAR for sample datasets
- Use VARP for full populations
- Use array formulas for conditional variance
- Convert imported text numbers to real numbers
- Avoid mixing text and numbers in the same column
- Use named ranges for cleaner formulas
Variance is the square of standard deviation.
If you need the standard deviation instead, use STDEV.
If you need the standard deviation instead, use STDEV.