Contact
12 programs
Explore the site

DVAR Function (LibreOffice Calc)

Database Advanced LibreOffice Calc Introduction not established

The DVAR function calculates the sample variance of numeric values in a database column that match a set of criteria. It is part of the database function family and supports structured, criteria-based filtering.

Verification status: Consult the documentation, examples and any stated test scope below. How formula examples are checked

Availability and version history

  • Introduced: Documented by LibreOffice 26.8.1.1; original introduction not established.
  • Availability: Supported in LibreOffice 26.8.1.1, verified against its function registry. This identifies a supported release, not a final working version.
  • Scope: Desktop Calc, using the English function name. Availability in other spreadsheet applications is separate.
  • Checked: 2026-10-14 — function reference; versioned support. Documentation/source check; not a new native test. Existing example-specific test claims below retain their stated scope.
The **DVAR** function calculates the **sample variance** of numeric values in a database column that match a set of criteria. It is part of Calc’s database function family, which uses structured criteria ranges to filter data before performing calculations. DVAR is ideal for conditional statistical analysis in tabular datasets without needing array formulas or complex filtering logic.

What the DVAR Function Does

  • Calculates sample variance of numeric values
  • Applies criteria-based filtering using a criteria range
  • Supports multiple criteria columns
  • Supports AND/OR logic via criteria layout
  • Works with structured database ranges

It is designed to be precise, statistical, and ideal for database-style variance analysis.

Syntax

DVAR(database; field; criteria)

Arguments

  • database:
    A range where the first row contains column labels.

  • field:
    The column to evaluate.

    • Use column label in quotes (recommended)
    • Or use column index (1 = first column)

  • criteria:
    A range containing column labels and one or more criteria rows.

Basic Examples

Assume a table in A1:C6:

A (Name) B (Age) C (Score)
John 25 80
Mary 30 90
Alex 22 70
John 28 85
Mary 35 88

Criteria in E1:F2:

Age Score
>25 >80

Sample variance of Score matching criteria

=DVAR(A1:C6; "Score"; E1:F2)

Values: 90, 85, 88
Sample variance = 6.333…

Sample variance of Age matching criteria

=DVAR(A1:C6; "Age"; E1:F2)

Values: 30, 28, 35
Sample variance = 13

Using field index

=DVAR(A1:C6; 3; E1:F2)

Evaluates Score.

Advanced Examples

Variance of Score where Name = “John”

Criteria:

Name
John

Formula:

=DVAR(A1:C6; "Score"; E1:E2)

Variance of Age between 25 and 30

Criteria:

Age
>=25
<=30

Formula:

=DVAR(A1:C6; "Age"; E1:E3)

OR logic (multiple rows)

Criteria:

Age Score
>30
>85

Formula:

=DVAR(A1:C6; "Score"; E1:F3)

Variance where Name begins with “M”

Criteria:

Name
M*

Formula:

=DVAR(A1:C6; "Score"; E1:E2)

Variance with dynamic criteria

=DVAR(A1:C6; "Score"; H1:I2)

Variance of positive Scores

Criteria:

Score
>0

Formula:

=DVAR(A1:C6; "Score"; E1:E2)

Variance of non-blank Scores

Criteria:

Score
<>""

Formula:

=DVAR(A1:C6; "Score"; E1:E2)

Edge Cases and Behavior Details

DVAR evaluates only numeric values

Text, blanks, and errors are ignored.

Sample variance requires at least 2 numeric values

If fewer than 2 values match → #DIV/0!

field can be:

  • Column label
  • Column index
  • Cell containing label

criteria must include column labels

Exact match required.

criteria supports:

  • Comparison operators
  • Wildcards
  • Multiple rows (OR)
  • Multiple columns (AND)

Empty criteria → variance of all numeric values

DVAR of an error in database → error ignored

DVAR of an error in criteria → error returned

Criteria rows:

  • Each row = OR
  • Each column = AND

Common Errors and Fixes

#DIV/0! — Not enough numeric values

Cause:

  • Only one or zero values match criteria

Fix:

  • Broaden criteria
  • Ensure numeric values exist

DVAR returns 0 unexpectedly

Cause:

  • Only one matching value (variance of a single value = 0)
  • Criteria labels don’t match database labels

Err:502 — Invalid argument

Occurs when:

  • field is invalid
  • database range malformed

Criteria not applied

Cause:

  • Criteria labels not identical to database labels

Best Practices

  • Use DVAR for structured statistical analysis
  • Use column labels instead of index numbers
  • Keep criteria ranges small and clearly labeled
  • Use multiple criteria rows for OR logic
  • Use multiple criteria columns for AND logic
  • Use DVARP for population variance
  • Use DSTDEV/DSTDEVP for standard deviation
  • Use FILTER (modern Calc) for dynamic extraction
DVAR is your structured statistical engine — perfect for criteria-based sample variance calculations in clean, database-style workflows.
  • Use DVARP for population variance
  • Use DSTDEV and DSTDEVP for standard deviation
  • Use DAVERAGE for conditional averages
  • Use DSUM for summation
  • Use DCOUNT or DCOUNTA for counting
  • Use VAR, VARP, VAR.S, VAR.P for non-database variance
  • Use FILTER for dynamic row extraction

By mastering DVAR and its companion database functions, you can build powerful, structured, and criteria-driven statistical workflows in LibreOffice Calc.