VARP Function (OpenOffice Calc)
The VARP function in OpenOffice Calc calculates the population variance of a dataset. Learn syntax, examples, statistical meaning, common errors, and best practices.
Verification status: Consult the documentation and examples below. Native application testing has not been documented for this guide. How formula examples are checked
The VARP function in OpenOffice Calc calculates the population variance, a core statistical measure that describes how spread out values are when your dataset represents the entire population, not just a sample. Variance is the square of standard deviation and is widely used in scientific analysis, finance, engineering, and quality control.
What the VARP Function Does
- Calculates population variance
- Measures how much values deviate from the population 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 when working with a sample.
Syntax
VARP(number1; number2; ...)
Arguments:
- number1, number2, … — Individual values, cell references, or ranges
Statistical Meaning
VARP uses the population variance formula:
[ \sigma^2 = \frac{\sum (x_i - \mu)^2}{n} ]
Where:
- ( x_i ) = each value
- ( \mu ) = population mean
- ( n ) = number of values
This formula divides by n, making it appropriate for complete populations.
Basic Examples
Population variance of a range
=VARP(A1:A10)
Population variance of multiple ranges
=VARP(A1:A10; C1:C10)
Population variance of a list of values
=VARP(10; 25; 7; 99; 3)
Population variance of dates
If A1:A5 contains dates:
=VARP(A1:A5)
Result: variance in days.
Advanced Examples
VARP across sheets
=VARP(Sheet1.A1:A100)
VARP with conditions (workaround)
OpenOffice Calc does not have VARPIF, but you can use:
=VARP(IF(A1:A100="North"; B1:B100))
Confirm with Ctrl+Shift+Enter.
VARP within a date range
=VARP(IF((A1:A100>=DATE(2025;1;1))*(A1:A100<=DATE(2025;12:31)); B1:B100))
Confirm with Ctrl+Shift+Enter.
VARP excluding zeros
=VARP(IF(A1:A100<>0; A1:A100))
Confirm with Ctrl+Shift+Enter.
VARP of filtered data
VARP does not ignore filtered rows.
Workaround:
=VARP(IF(SUBTOTAL(103; OFFSET(A1; ROW(A1:A100)-ROW(A1); 0)); A1:A100))
Confirm with Ctrl+Shift+Enter.
VARP in a 3D range
=VARP(Sheet1:Sheet5.A1:A10)
Common Errors and Fixes
VARP returns Err:502 (Invalid argument)
Occurs when:
- No numeric values exist
- All values are text or empty
- Imported numbers stored as text
Fix: Convert text to numbers:
Data → Text to Columns → OK
VARP returns 0 unexpectedly
Possible causes:
- All values are identical
- Only one numeric value exists
VARP includes values you expected it to ignore
VARP includes:
- Dates
- Times
- Numeric results of formulas
VARP excludes values you expected it to include
VARP 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 VARP for complete population datasets
- Use VAR for sample datasets
- 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
If you need the standard deviation instead, use STDEVP.