CONFIDENCE.NORM Function (LibreOffice Calc)
The CONFIDENCE.NORM function in LibreOffice Calc returns the margin of error for a confidence interval assuming a normal distribution. This guide explains syntax, interpretation, examples, errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✖ |
| Wps | ✔ |
| Zoho | ✔ |
What the CONFIDENCE.NORM Function Does ▾
- Computes the margin of error for a confidence interval
- Assumes a normal distribution
- Uses z-scores (not t-scores)
- Useful for large samples or known population SD
- Works across sheets
The confidence interval is:
[ \bar{x} \pm \text{CONFIDENCE.NORM} ]
Syntax ▾
CONFIDENCE.NORM(alpha; standard_dev; size)
Where:
alpha— significance level (e.g., 0.05 for 95% confidence)standard_dev— population standard deviationsize— sample size
Use CONFIDENCE.T when population SD is unknown or sample size is small.
Interpretation of Alpha ▾
| Alpha | Confidence Level |
|---|---|
| 0.10 | 90% |
| 0.05 | 95% |
| 0.01 | 99% |
Basic Examples ▾
95% confidence interval margin of error
=CONFIDENCE.NORM(0.05; 10; 100)
99% confidence interval
=CONFIDENCE.NORM(0.01; 12; 64)
Using sample SD as approximation
=CONFIDENCE.NORM(0.05; STDEV.S(A1:A100); COUNT(A1:A100))
Across sheets
=CONFIDENCE.NORM(0.05; Sheet1.B1; Sheet2.C1)
Advanced Examples ▾
Full confidence interval (lower and upper bounds)
Lower bound:
=AVERAGE(A1:A100) - CONFIDENCE.NORM(0.05; STDEV.S(A1:A100); COUNT(A1:A100))
Upper bound:
=AVERAGE(A1:A100) + CONFIDENCE.NORM(0.05; STDEV.S(A1:A100); COUNT(A1:A100))
Confidence interval ignoring errors
=CONFIDENCE.NORM(0.05; STDEV.S(IF(ISNUMBER(A1:A100); A1:A100)); COUNT(IF(ISNUMBER(A1:A100); A1:A100)))
(Confirm with Ctrl+Shift+Enter in older Calc.)
Confidence interval after removing outliers
=CONFIDENCE.NORM(0.05; STDEV.S(FILTER(A1:A100; A1:A100<1000)); COUNT(FILTER(A1:A100; A1:A100<1000)))
Confidence interval for proportions (approximation)
=CONFIDENCE.NORM(0.05; SQRT(p*(1-p)); n)
Where p = proportion.
Confidence interval for large-sample surveys
=CONFIDENCE.NORM(0.05; PopulationSD; SampleSize)
Confidence interval for standardized data
=CONFIDENCE.NORM(0.05; 1; COUNT(A1:A100))
(Standardized SD = 1)
How CONFIDENCE.NORM Calculates Margin of Error ▾
- Compute z-score for alpha:
[ z = \Phi^{-1}(1 - \alpha/2) ]
- Compute standard error:
[ SE = \frac{\sigma}{\sqrt{n}} ]
- Compute margin of error:
[ ME = z \cdot SE ]
Common Errors and Fixes ▾
Err:502 — Invalid argument
Occurs when:
- Alpha ≤ 0 or ≥ 1
- Standard deviation ≤ 0
- Sample size < 1
Err:504 — Parameter error
Occurs when:
- Semicolons are incorrect
- Range references malformed
Margin of error seems too large
Possible causes:
- Small sample size
- Large standard deviation
- Very high confidence level (small alpha)
Best Practices ▾
- Use CONFIDENCE.NORM for large samples or known population SD
- Use CONFIDENCE.T for small samples
- Remove outliers before computing SD
- Use named ranges for cleaner formulas
- Report both margin of error and confidence interval bounds
- Use STANDARDIZE and Z.TEST for deeper analysis
CONFIDENCE.NORM gives you the statistical “radius” around your sample mean — the tighter the interval, the more precisely you’ve measured your population.