Confidence Interval In Excel

What Is Confidence Interval In Excel?

The confidence interval in Excel is a range of values set around the known sample mean. The CONFIDENCE() utilizes a Normal Distribution to determine the confidence value, which is useful for determining the confidence interval for the population mean.

Users can use the CONFIDENCE() while performing statistical and financial analysis. But the function got replaced by the functions CONFIDENCE.NORM and CONFIDENCE.T, with superior accuracy, in Excel 2010. And thus, it is now available as an inbuilt Compatibility Excel function for backward compatibility.

For example, the significance level is 0.05 for the mean of a sample lap time on track for 35 athletes. And the sample mean is 40 seconds, with the standard deviation being 2 seconds.

And the requirement is to compute the confidence interval for the given population mean in cell B7.

Then, we can apply the CONFIDENCE() in the target cell and calculate confidence interval in Excel by supplying the given data as the function arguments.

The above example explains the 95 confidence interval in Excel scenario, as alpha is 0.05, which indicates a confidence level of 95%.

Thus, the required confidence interval is 40 ± 0.66258876, which equals the range of 39.33741 to 40.66259 seconds.

Confidence Interval() Excel Formula

The function to determine the confidence interval in Excel is:

where,

• alpha: The significance level required for determining the confidence level. It is the difference between the value one and confidence. So, a significance value of 0.05 represents a 95% confidence level and thus helps calculate a 95 confidence interval in Excel.
• standard_dev: The known population standard deviation for the value range.
• size: The sample data size.

The three arguments in the above CONFIDENCE() syntax are mandatory.

How To Calculate Confidence Interval In Excel?

We can calculate the Confidence Interval function in Excel in 2 ways, namely:

1. Access from the Excel ribbon.
2. Enter in the worksheet manually.

Method #1 – Access From The Excel Ribbon

Select the target cell and click the Formulas tab → click the More Functions option drop-down → click the Compatibility option right arrow → select the CONFIDENCE function, as shown below:

The Function Arguments window appears. Enter the arguments in the Alpha, Standard_dev, and Size fields → click OK, as shown below.

Method #2 – Enter In The Worksheet Manually

1. Select the target cell.
2. Type =CONFIDENCE( in the cell. [Alternatively, type =C or =CONF and double-click the CONFIDENCE function from the Excel suggestions.]
3. Enter the arguments as cell values or references and close the brackets.
4. Press Enter to execute and get the CONFIDENCE() output, the confidence value.
5. Finally, calculate the confidence interval in Excel using the below formula:

Let us now see how to calculate confidence interval in Excel with an example.

Consider the significance level is 0.01 for the mean of a sample age of 50 people. And the sample mean is 25 years, with the standard deviation being 1.5 years.

And the requirement is to determine the confidence interval for the given population mean in cell B7.

Examples

Check out the following illustrations to learn how to calculate the confidence interval in Excel.

Example #1

Suppose we have a sample of 12 months’ average temperatures recorded in Texas.

And the requirement is to compute the confidence interval for the population in cell E8 in the second table.

In this example, we need to calculate the CONFIDENCE() argument values to apply the function in cell E7 and compute the required confidence interval in the target cell E8.

• Step 1: First, select cell E3, enter the AVERAGE excel function to find the sample mean, and then, press Enter.

=AVERAGE(B3:B14)

• Step 2: Next, select cell E4, enter the STDEV.P() to determine the population standard deviation for the given dataset, and then press Enter.

=STDEV.P(B3:B14)

• Step 3: Then, select cell E5, and enter the total number of observations in the sample data.

• Step 4: Now, select cell E6, and then, enter the significance level, alpha, as 0.03, implying a 97% confidence level.

• Step 5: Next, select cell E7, enter the CONFIDENCE(), and then, press Enter.

=CONFIDENCE(E6,E4,E5)

• Step 6: Now, enter the confidence interval in Excel cell E8 using the below formula.

=CONCATENATE(ROUND(E3,1),” ± “,ROUND(E7,2))

The ROUND excel function rounds the mean and confidence values to 1 and 2 decimal places, respectively. And the CONCATENATE excel function concatenates the rounded-up values and the ‘±’ symbol.

Thus, the required confidence interval of values centering around the sample mean of 70.5 is 70.5 ± 6.74, equaling the value range of 63.76 to 77.24 °F.

Please Note: The suggestion is to use the STDEV.S() and STDEV.P() to determine the sample and population standard deviation, respectively. And apply the CONFIDENCE.T() when we do not have the population standard deviation information and the sample size is below 30.

Further, consider the following steps to determine the sample means and confidence values for three more states, Georgia, California, and Washington. Assume the same significance level of 0.03.

And the requirement is to plot the confidence interval in Excel graph for the above data.

Then, the steps are as follows:

• Step 1: To begin with, select the cell range B2:C6 and then, pick the Excel Bar Chart type from the Insert tab.

Next, select the 2-D Clustered Column chart.

• Step 2: Next, click Chart Elements (‘+’ icon) → The arrow against the Error Bars option → More Options to open the Format Error Bars pane.

• Step 3: In the Error Bars Options tab of the Format Error Bars pane, first, click Custom, and then, click the Specify Value option to open the Custom Error Bars window.

• Step 4: Next, enter the Positive Error Value, and Negative Error Value ranges as cell range D3:D6.

• Step 5: Then, click OK to close the Custom Error Bars window and close the Format Error Bars pane to view the plot as depicted below:

• Step 6: Now, click the chart area to enable the Chart Elements option, and then, check the Axis Titles option.

• Step 7: Update the chart and axis titles using the corresponding elements in the chart area.

Thus, in the above confidence interval in Excel graph, the bars represent the states’ sample mean values, and the error bars indicate the corresponding confidence values.

And let us consider the Washington state’s data for plot interpretation. The mean value for Washington is 58.9°F. And the 97% confidence interval for this mean ranges from 56.96 to 60.84°F, which the plot corroborates.

Example #2

The CONFIDENCE.NORM() and CONFIDENCE.T() are inbuilt Statistical functions, and we can access them by navigating the path Formulas → More Functions → Statistical.

The CONFIDENCE.NORM() definition is the same as CONFIDENCE(). On the other hand, the CONFIDENCE.T() determines the confidence interval for a population mean utilizing the Student’s t distribution.

Furthermore, all three functions have the same syntax (Refer to the Confidence Interval() Excel Formula).

We shall see how the latest functions, CONFIDENCE.NORM and CONFIDENCE.T, work with an example.

Consider the significance level of 0.01 for the mean sample height values for 100 men. And the sample mean is 70 inches, with the standard deviation being 3.5 inches.

And the requirement is to determine the confidence interval for the population mean. Assume we must display the results in cell range C10:C12.

Then, here is how we can apply the three abovementioned functions and obtain the confidence value to calculate the confidence interval.

• Step 1: Select the target cell C10, enter the CONFIDENCE(), and press Enter.

=CONFIDENCE(D4,D5,D6)

• Step 2: Select the target cell C11, enter the CONFIDENCE.NORM(), and press Enter.

=CONFIDENCE.NORM(D4,D5,D6)

• Step 3: Select the target cell C12, enter the CONFIDENCE.T(), and press Enter.

=CONFIDENCE.T(D4,D5,D6)

The above output shows the CONFIDENCE() and CONFIDENCE.NORM() return the same confidence value. And, as the CONFIDENCE.T() uses the Student’s t distribution to compute the confidence value, it returns a different value.

Thus, the confidence intervals are 70 ± 0.901 inches (Based on CONFIDENCE() and CONFIDENCE.NORM() output) and 70 ± 0.919 inches (Based on CONFIDENCE.T() output).

Example #3

Consider a class’s test scores following a normal distribution. And we will take a random sample of 7 students’ test scores.

And the requirement is to calculate a 95% confidence interval for the average score.

Then, we can use the confidence interval in Excel Data Analysis option, Descriptive Statistics, to get the required output.

But first, we must install the Analysis ToolPak add-in to use the Data Analysis feature from the Data tab.

• Step 1: The below image shows the Data tab does not contain the Data Analysis feature. So, click File.

• Step 2: Click Options.

• Step 3: The Excel Options window opens up. Here, we must click Add-ins → Analysis ToolPak → Go.

• Step 4: The Add-ins window appears, where we need to check the box against Analysis ToolPak and click OK to view the Data Analysis feature in the Data tab.

We shall now see how to use the Descriptive Statistics option to obtain the required confidence interval in Excel Data Analysis.

• Step 1: Go to Data → Data Analysis to open the Data Analysis window.

• Step 2: Pick Descriptive Statistics from the list of Analysis Tools in the Data Analysis window to access the Descriptive Statistics window.

• Step 3: Update the fields in the Descriptive Statistics window, as depicted below.

The sample data is the input range. And as we require the labels in the first row, select the cells in the input range as C1:C8 to include the column header.

The Grouped By should be Columns, and let the Output Range be a cell address in the active sheet to display the output in the same sheet as the sample data.

Check the Summary statistics to view the entire confidence value calculation. And as the required confidence level is 95%, check the box against the field. Finally, check the boxes against the last two fields and enter their values as one to show the highest and lowest scores in the outcome.

• Step 4: Finally, click OK to obtain the below output.

Thus, the margin of error, or the confidence value, is 1.6719244. So, the confidence interval is 85.89 ± 1.67, or the score range of 84.22 to 87.56.

Further, the third table shows the confidence interval calculations using the CONFIDENCE function.

Refer to column J for the formulas in column I applied to get the required output.

We use the STDEV.S() to determine the sample standard deviation. And we apply the CONFIDENCE.T() as we do not have the population standard deviation information, and the sample size is below 30.

Thus, both methods result in the same confidence value of 1.6719244.

Important Things To Note

• For a non-numeric argument value, the CONFIDENCE() throws the #VALUE! error.
• The CONFIDENCE() truncates a non-integer size argument value while determining the confidence interval in Excel.
• For an alpha ≤ 0 or ≥ 1, standard_dev less than or equal to 0, or size less than 1, the CONFIDENCE() output is the #NUM! error.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the Confidence Interval In Excel, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Confidence Interval In Excel. Here we calculate the confidence value & interval using CONFIDENCE() formula along with examples, & downloadable template. You can learn more from the following articles – 

• Exponential Smoothing in Excel
• Non-linear Regression in Excel
• Lognormal Distribution In Excel