Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel

Confidence interval is calculated to solve statistical questions. Finding out this number without the help of a computer is quite difficult, so you should use the Excel tools if you need to find out the acceptable range of deviation from the sample mean.

Computing a Confidence Interval with the CONFID.NORM Operator

The operator belongs to the “Statistical” category. In earlier versions, it is called “TRUST”, its function consisted of the same arguments.

The complete function looks like this: =CONFIDENCE.NORM(Alpha,Standard,Size).

Consider the operator formula by arguments (each of them must appear in the calculation):

  1. “Alpha” indicates the level of significance on which the calculation is based.

There are two ways to calculate the additional level:

  • 1-(Alpha) – suitable if the argument is a coefficient. Example: 1-0,4=0,6 (0,4=40%/100%);
  • (100-(Alpha))/100 – the formula is used when calculating the interval as a percentage. Example: (100-40)/100=0,6.
  1. The standard deviation is the allowable deviation in a particular sample.
  2. Size – the amount of information analyzed

Pay attention! The TRUST operator can still be found in Excel. If you need to use it, look for it in the “Compatibility” section.

Let’s check the formula in action. You need to create a table with multiple statistical calculation values. Assume that the standard deviation is 7. The goal is to define an interval with a confidence level of 80%.

Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel
1

It is not necessary to enter the deviation and the level of confidence on the sheet, these data can be entered manually. The calculation takes place in several steps:

  1. Select an empty cell and open the “Function Manager”. It will appear on the screen after clicking on the “F (x)” icon next to the formula bar. You can also get to the function menu through the “Formulas” tab on the toolbar, in its left part there is the “Insert function” button with the same sign.
Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel
2
  1. Select the “Statistical” section and find among the list items the operator TRUST.NORM. You need to click on it and click “OK”.
Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel
3
  1. The Arguments Fill window will open. The first line should contain the formula for calculating the “Alpha” argument. According to the condition, the trust level is expressed as a percentage, so we use the second formula: (100-(Alpha))/100.
  2. The standard deviation is already known, let’s write it in a line or select a cell with data placed on the page. The third line contains the number of records in the table – there are 10 of them. After filling in all the fields, press “Enter” or “OK”.
Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel
4

The function can be automated so that changing the information does not cause the calculation to fail. Let’s find out how to do it step by step.

  1. When the “Size” field is not yet filled, click on it, making it active. Then we open the function menu – it is located on the left side of the screen on the same line with the formula bar. To open it, click on the arrow. You need to select the “Other functions” section, this is the last entry in the list.
Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel
5
  1. The Function Manager will reappear. Among the statistical operators, you need to find the “Account” function and select it.
Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel
6

Important! The COUNT function arguments can be numbers, cells, or groups of cells. In this case, the latter will do. In total, the formula can have no more than 255 arguments.

  1. The top field should contain the values ​​grouped into the cell range. Click on the first argument, select the column without the header, and click the OK button.
Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel
7

The interval value will appear in the cell. This number was obtained using the example data: 2,83683532.

Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel
8

Determination of the confidence interval through CONFIDENCE.STUDENT

This operator is also intended to calculate the deviation range. In the calculations, a different strategy is used – it uses the Student’s distribution, provided that the spread of the value is unknown.

The formula differs from the previous one only in the operator. It looks like this: =TRUST.STUDENT(Alpha;Ctand_off;size).

We use the saved table for new calculations. The standard deviation in the new problem becomes an unknown argument.

  1. Open the “Function Manager” in one of the ways described above. You need to find the CONFIDENCE.STUDENT function in the “Statistical” section, select it and click “OK”.
Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel
9
  1. Fill in the function arguments. The first line is the same formula: (100-(Alpha))/100.
  2. The deviation is unknown, according to the condition of the problem. To calculate it, we use an additional formula. You need to click on the second field in the arguments window, open the functions menu and select the “Other functions” item.
Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel
10
  1. Requires STDDEV.B (by sample) operator in the Statistical section. Select it and click OK.
Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel
11
  1. We fill the first argument of the opened window with a range of cells with values ​​without taking into account the header. You don’t need to click OK after that.
Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel
12
  1. Let’s go back to the TRUST.STUDENT arguments by double-clicking on this inscription in the formula bar. In the “Size” field, set the COUNT operator, as last time.
Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel
13

After pressing “Enter” or “OK” the new value of the confidence interval will appear in the cell. According to Student, it turned out to be less – 0,540168684.

Determining the boundaries of the interval on both sides

To calculate the boundaries of the interval, you need to find out what is the average value for it, using the AVERAGE function.

  1. Open the “Function Manager” and select the desired operator in the “Statistical” section.
Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel
14
  1. Add a group of cells containing values ​​to the first argument field and click the OK button.
Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel
15
  1. Now you can define the right and left borders. It will take some simple math. Calculation of the right border: select an empty cell, add cells in it with a confidence interval and an average value.
Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel
16
  1. To determine the left margin, the confidence interval must be subtracted from the mean.
Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel
17
  1. We perform the same operations with the Student’s confidence interval. As a result, we obtain the boundaries of the interval in two versions.
Confidence interval in Excel. 2 Ways to Calculate Confidence Interval in Excel
18

Conclusion

Excel’s “Function Manager” makes it easy to find the confidence interval. It can be determined in two ways, which use different methods of calculation.

Leave a Reply