**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**

`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**A B C D E F 1 **Alpha****Standard Dev****Sample Size****Formula****Result**2 1 0.05 150 30 =CONFIDENCE(B2,C2,D2) 27.39 3 2 0.05 155 31 =CONFIDENCE(B3,C3,D3) 28.05 4 3 0.05 145 29 =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**A B C D E F 1 **Alpha****Standard Dev****Sample Size****Formula****Result**2 1 0.05 50,000 10 =CONFIDENCE(B2,C2,D2) 15,811.39 3 2 0.05 52,000 11 =CONFIDENCE(B3,C3,D3) 15,678.74 4 3 0.05 48,000 9 =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**A B C D E F 1 **Alpha****Standard Dev****Sample Size****Formula****Result**2 1 0.05 1.2 40 =CONFIDENCE(B2,C2,D2) 0.37 3 2 0.05 1.3 42 =CONFIDENCE(B3,C3,D3) 0.40 4 3 0.05 1.1 38 =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**A B C D E F 1 **Alpha****Standard Dev****Sample Size****Formula****Result**2 1 0.05 300 25 =CONFIDENCE(B2,C2,D2) 117.78 3 2 0.05 320 26 =CONFIDENCE(B3,C3,D3) 124.91 4 3 0.05 290 24 =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**A B C D E F 1 **Alpha****Standard Dev****Sample Size****Formula****Result**2 1 0.05 2,000 15 =CONFIDENCE(B2,C2,D2) 1,011.79 3 2 0.05 2,100 16 =CONFIDENCE(B3,C3,D3) 1,040.83 4 3 0.05 1,900 14 =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**A B C D E F 1 **Alpha****Standard Dev****Sample Size****Formula****Result**2 1 0.05 200 25 =IF(D2>20, CONFIDENCE(B2,C2,D2), “Sample too small”) 78.46 3 2 0.05 210 18 =IF(D3>20, CONFIDENCE(B3,C3,D3), “Sample too small”) “Sample too small.” 4 3 0.05 190 22 =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**A B C D E F 1 **Alpha****Product A Sales****Product B Sales****Formula****Result**2 1 0.05 5,000 6,000 =CONFIDENCE(B2,SUM(C2:D2),30) 78.46 3 2 0.05 5,200 6,100 =CONFIDENCE(B3,SUM(C3:D3),31) 80.05 4 3 0.05 4,900 5,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**A B C D E F 1 **Alpha****Product****Sample Size****Formula****Result**2 1 0.05 A 30 =CONFIDENCE(B2,VLOOKUP(C2, H2:I4, 2, FALSE),D2) 78.46 3 2 0.05 B 31 =CONFIDENCE(B3,VLOOKUP(C3, H2:I4, 2, FALSE),D3) 80.05 4 3 0.05 C 29 =CONFIDENCE(B4,VLOOKUP(C4, H2:I4, 2, FALSE),D4) 75.92 **Lookup Table**H I 1 **Product****Standard Dev**2 A 200 3 B 210 4 C 190 **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**A B C D E F 1 **Alpha****Jan Sales****Feb Sales****Formula****Result**2 1 0.05 6,000 6,500 =CONFIDENCE(B2,AVERAGE(C2:D2),30) 78.46 3 2 0.05 6,200 6,700 =CONFIDENCE(B3,AVERAGE(C3:D3),31) 80.05 4 3 0.05 5,900 6,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

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