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.

2. The standard deviation is the allowable deviation in a particular sample.
3. 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%.

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.

2. Select the “Statistical” section and find among the list items the operator TRUST.NORM. You need to click on it and click “OK”.

3. 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.
4. 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”.

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.

2. The Function Manager will reappear. Among the statistical operators, you need to find the “Account” function and select it.

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.

3. 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.

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

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”.

2. Fill in the function arguments. The first line is the same formula: (100-(Alpha))/100.
3. 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.

4. Requires STDDEV.B (by sample) operator in the Statistical section. Select it and click OK.

5. 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.

6. 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.

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.

2. Add a group of cells containing values ​​to the first argument field and click the OK button.

3. 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.

4. To determine the left margin, the confidence interval must be subtracted from the mean.

5. We perform the same operations with the Student’s confidence interval. As a result, we obtain the boundaries of the interval in two versions.

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.