CONFIDENCE function
CONFIDENCE returns the margin of error to add and subtract from a sample mean, using the normal distribution. It is a compatibility function; use CONFIDENCE.NORM in new workbooks.
Example
Read it as a sentence: find the margin of error at the 0.05 significance level for a standard deviation of 2.5 and a sample of 50.
A
B
C
D
1
0.692951912
2
3
Syntax
CONFIDENCE(alpha, standard_dev, size)
-
alpha(required) - Significance level, e.g. 0.05 for 95% confidence.
-
standard_dev(required) - Population standard deviation, assumed known.
-
size(required) - Sample size.
Returns: Half-width of the confidence interval for a mean.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=CONFIDENCE(0.05,2.5,50) | 0.692951912 | Working example |
=CONFIDENCE("0.05",2.5,50) | 0.692951912 | Text-number in first arg |
=CONFIDENCE({0.05,0.05},2.5,50) | 0.692951912 | Array in first arg (lifts, spills) |
=CONFIDENCE(0.05,2.5,1) | 4.899909961 | Size 1 |
=CONFIDENCE(0.05,2.5,50.9) | 0.692951912 | Fractional size truncated |
=CONFIDENCE("abc",2.5,50) | #VALUE! | Non-numeric text in first arg |
=CONFIDENCE(NA(),2.5,50) | #N/A | Error in first arg |
=CONFIDENCE(0,2.5,50) | #NUM! | Alpha 0 |
Watch out for
- Superseded by CONFIDENCE.NORM; use that in new workbooks.
- Uses the normal curve, so it assumes a known standard deviation; CONFIDENCE.T uses the t distribution instead.
Where it works
- Excel
- CONFIDENCE is in every version of Excel from 2007 on. Compatibility function: still works in every version, but newer replacements exist.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Compatibility functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .