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.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
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 VARP when your dataset represents the entire population.
Use VAR when working with a sample.
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
Variance is the square of standard deviation.
If you need the standard deviation instead, use STDEVP.
If you need the standard deviation instead, use STDEVP.