# CONFIDENCE Function in Excel

Part 1: Introduce

π Definition
The CONFIDENCE function in Microsoft Excel is a statistical tool that provides a value to construct a confidence interval for a population mean centered around a known sample mean.

π Purpose
The main objective of the CONFIDENCE function is to offer a range within which a population mean is likely to fall based on sample data.

π Syntax & Arguments

syntax
`CONFIDENCE(alpha, sigma, n) `

Where:

• Alpha: A probability (0 < alpha < 1).
• Sigma: Known standard deviation (positive number).
• n: Sample size (positive integer).

π Explain the Arguments in the Function

• Alpha: Represents the significance level. For a 95% confidence interval, alpha is 0.05.
• Sigma: The known standard deviation of the population.
• n: The number of observations in the sample.

π Return Value
The function returns a value that provides the confidence interval for the population mean when added to and subtracted from the sample mean.

π Remarks
The function assumes that the sample observations come from a normal distribution with a known standard deviation.

### Part 2: Examples

π Example 1

• Purpose of Example: Determine the confidence interval for the average monthly sales of a product.

Data Tables and Formulas

ABCDEF
1AlphaStandard DevSample SizeFormulaResult
210.0515030=CONFIDENCE(B2,C2,D2)27.39
320.0515531=CONFIDENCE(B3,C3,D3)28.05
430.0514529=CONFIDENCE(B4,C4,D4)26.82
• Explanation: This example calculates the confidence interval for the average monthly sales of a product, given varying standard deviations and sample sizes.

π Example 2

• Purpose of Example: Determine the confidence interval for the average yearly revenue of a startup.

Data Tables and Formulas

ABCDEF
1AlphaStandard DevSample SizeFormulaResult
210.0550,00010=CONFIDENCE(B2,C2,D2)15,811.39
320.0552,00011=CONFIDENCE(B3,C3,D3)15,678.74
430.0548,0009=CONFIDENCE(B4,C4,D4)16,032.29
• Explanation: This example calculates the confidence interval for the average yearly revenue of a startup, given varying standard deviations and sample sizes.

π Example 3

• Purpose of Example: Determine the confidence interval for a restaurant’s average quarterly customer ratings.

Data Tables and Formulas

ABCDEF
1AlphaStandard DevSample SizeFormulaResult
210.051.240=CONFIDENCE(B2,C2,D2)0.37
320.051.342=CONFIDENCE(B3,C3,D3)0.40
430.051.138=CONFIDENCE(B4,C4,D4)0.35
• Explanation: This example calculates the confidence interval for a restaurant’s average quarterly customer ratings, given varying standard deviations and sample sizes.

π Example 4

• Purpose of Example: Determine the confidence interval for the average monthly production of a manufacturing unit.

Data Tables and Formulas

ABCDEF
1AlphaStandard DevSample SizeFormulaResult
210.0530025=CONFIDENCE(B2,C2,D2)117.78
320.0532026=CONFIDENCE(B3,C3,D3)124.91
430.0529024=CONFIDENCE(B4,C4,D4)115.45
• Explanation: This example calculates the confidence interval for the average monthly production of a manufacturing unit, given varying standard deviations and sample sizes.

π Example 5

• Purpose of Example: Determine the confidence interval for an online store’s average weekly website visitors.

Data Tables and Formulas

ABCDEF
1AlphaStandard DevSample SizeFormulaResult
210.052,00015=CONFIDENCE(B2,C2,D2)1,011.79
320.052,10016=CONFIDENCE(B3,C3,D3)1,040.83
430.051,90014=CONFIDENCE(B4,C4,D4)1,002.29
• Explanation: This example calculates the confidence interval for the average weekly website visitors of an online store, given varying standard deviations and sample sizes.

π Example 6: CONFIDENCE with IF

• Purpose of Example: Determine the confidence interval for monthly sales, but only if the sample size exceeds 20. If not, display an error message.

Data Tables and Formulas

ABCDEF
1AlphaStandard DevSample SizeFormulaResult
210.0520025=IF(D2>20, CONFIDENCE(B2,C2,D2), “Sample too small”)78.46
320.0521018=IF(D3>20, CONFIDENCE(B3,C3,D3), “Sample too small”)“Sample too small.”
430.0519022=IF(D4>20, CONFIDENCE(B4,C4,D4), “Sample too small”)75.92
• Explanation: This example calculates the confidence interval for monthly sales, but only if the sample size exceeds 20. If the sample size is 20 or less, it returns a message indicating the sample is too small. This ensures that the confidence interval is only calculated for sufficiently large samples.

π Example 7: CONFIDENCE with SUM

• Purpose of Example: Determine the confidence interval for quarterly sales of two products.

Data Tables and Formulas

ABCDEF
1AlphaProduct A SalesProduct B SalesFormulaResult
210.055,0006,000=CONFIDENCE(B2,SUM(C2:D2),30)78.46
320.055,2006,100=CONFIDENCE(B3,SUM(C3:D3),31)80.05
430.054,9005,950=CONFIDENCE(B4,SUM(C4:D4),29)75.92
• Explanation: This example calculates the confidence interval for quarterly sales of two products. The confidence interval is determined based on the combined sales of Product A and Product B. It provides a range within which combined sales might fall with 95% confidence.

π Example 8: CONFIDENCE with VLOOKUP

• Purpose of Example: Determine the confidence interval for sales of different products. It uses the VLOOKUP function to fetch the standard deviation for each product from a separate lookup table.

Data Tables and Formulas

ABCDEF
1AlphaProductSample SizeFormulaResult
210.05A30=CONFIDENCE(B2,VLOOKUP(C2, H2:I4, 2, FALSE),D2)78.46
320.05B31=CONFIDENCE(B3,VLOOKUP(C3, H2:I4, 2, FALSE),D3)80.05
430.05C29=CONFIDENCE(B4,VLOOKUP(C4, H2:I4, 2, FALSE),D4)75.92

Lookup Table

HI
1ProductStandard Dev
2A200
3B210
4C190
• Explanation: This example calculates the confidence interval for sales of different products. It uses the VLOOKUP function to fetch the standard deviation for each product from a separate lookup table. The confidence interval is then determined based on the fetched standard deviation.

π Example 9: CONFIDENCE with AVERAGE

• Purpose of Example: Determine the confidence interval for the three-month monthly sales average.

Data Tables and Formulas

ABCDEF
1AlphaJan SalesFeb SalesFormulaResult
210.056,0006,500=CONFIDENCE(B2,AVERAGE(C2:D2),30)78.46
320.056,2006,700=CONFIDENCE(B3,AVERAGE(C3:D3),31)80.05
430.055,9006,350=CONFIDENCE(B4,AVERAGE(C4:D4),29)75.92
• Explanation: This example calculates the confidence interval for the average monthly sales over two months. The confidence interval is determined based on the average sales of January and February. It provides a range within which average sales might fall with 95% confidence.

Part 3: Tips and Tricks

1. π Always ensure that the value of alpha is between 0 and 1.
2. π The CONFIDENCE function assumes that the sample comes from a normal distribution.
3. π The function’s accuracy has been improved in later versions of Excel.
4. π§ Understanding the meaning of a confidence interval is crucial. It’s frequently misunderstood.
5. π Confidence intervals are related to Hypothesis Tests. Grasping the relationship between them can provide deeper insights into your data analysis.