STDEV Function (OpenOffice Calc)
The STDEV function in OpenOffice Calc calculates the sample standard deviation 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 STDEV Function Does â–¾
- Calculates sample standard deviation
- Measures how much values vary 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 STDEV when working with a sample.
Syntax â–¾
STDEV(number1; number2; ...)
Arguments:
- number1, number2, … — Individual values, cell references, or ranges
Statistical Meaning â–¾
STDEV uses the sample standard deviation formula:
[ s = \sqrt{\frac{\sum (x_i - \bar{x})^2}{n - 1}} ]
Where:
- ( x_i ) = each value
- ( \bar{x} ) = sample mean
- ( n ) = number of values
This makes STDEV appropriate when your data represents a subset of a larger population.
Basic Examples â–¾
Standard deviation of a range
=STDEV(A1:A10)
Standard deviation of multiple ranges
=STDEV(A1:A10; C1:C10)
Standard deviation of a list of values
=STDEV(10; 25; 7; 99; 3)
Standard deviation of dates
If A1:A5 contains dates:
=STDEV(A1:A5)
Result: variability in days.
Advanced Examples â–¾
STDEV across sheets
=STDEV(Sheet1.A1:A100)
STDEV with conditions (workaround)
OpenOffice Calc does not have STDEVIF, but you can use:
=STDEV(IF(A1:A100="North"; B1:B100))
Confirm with Ctrl+Shift+Enter.
STDEV within a date range
=STDEV(IF((A1:A100>=DATE(2025;1;1))*(A1:A100<=DATE(2025;12;31)); B1:B100))
Confirm with Ctrl+Shift+Enter.
STDEV excluding zeros
=STDEV(IF(A1:A100<>0; A1:A100))
Confirm with Ctrl+Shift+Enter.
STDEV of filtered data
STDEV does not ignore filtered rows.
Workaround:
=STDEV(IF(SUBTOTAL(103; OFFSET(A1; ROW(A1:A100)-ROW(A1); 0)); A1:A100))
Confirm with Ctrl+Shift+Enter.
STDEV in a 3D range
=STDEV(Sheet1:Sheet5.A1:A10)
Common Errors and Fixes â–¾
STDEV 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
STDEV returns 0 unexpectedly
Possible causes:
- All values are identical
- Only one numeric value exists
STDEV includes values you expected it to ignore
STDEV includes:
- Dates
- Times
- Numeric results of formulas
STDEV excludes values you expected it to include
STDEV 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 STDEV for sample datasets
- Use STDEVP for full populations
- Use array formulas for conditional standard deviation
- Convert imported text numbers to real numbers
- Avoid mixing text and numbers in the same column
- Use named ranges for cleaner formulas