Contact
12 programs
Explore the site

VAR Function (OpenOffice Calc)

Statistical Intermediate OpenOffice Calc OpenOffice.org 3.0

The VAR function in OpenOffice Calc calculates the sample 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 VAR function in OpenOffice Calc calculates the sample variance, a fundamental statistical measure that describes how spread out values are around the mean. Variance is the square of standard deviation and is widely used in scientific analysis, finance, engineering, and quality control.

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.

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.