One of the methods for solving statistical problems is the calculation of the confidence interval. It is used as a more preferred alternative to the point estimate with a small sampling. It should be noted that the process of calculating the confidence interval is rather complicated. But the tools of the Excel program allow you to easily simplify it. Let's find out how it is done in practice.
Method 2: Feature trust.
In addition, there is another feature that is associated with the calculation of the confidence interval - trust. It appeared, only starting with Excel 2010. This operator performs the calculation of the general population confidence interval using Student's distribution. It is very convenient to use when the dispersion and, accordingly, the standard deviation is unknown. The syntax of the operator is:
= Confidence .styudient (alpha; standard_otchal; size)
As we see, the names of the operators and in this case remained unchanged.
Let's see how to calculate the boundaries of the confidence interval with an unknown standard deviation on the example of the same totality that we have considered in the previous method. The level of confidence, as the last time, take 97%.
- We highlight the cell in which the calculation will be made. Clay on the "Insert function" button.
- In the operating wizard of the functions, go to the category "Statistical". Choose the name "trust. Student". Clay on the "OK" button.
- The arguments of the specified operator's arguments are launched.
In the Alpha field, given that the level of confidence is 97%, record the number 0.03. The second time on the principles of calculation of this parameter will not stop.
After that, set the cursor in the "Standard Deviation" field. This time this indicator is unknown and it is required to calculate it. This is done using a special function - Standotclone. To call the window of this operator, click on the triangle to the left of the formula string. If you do not find the desired name in the list that opens, then go through the "Other functions ...".
- The functions master starts. We move to the category "Statistical" and note in it the name "Standotclona.B". Then clay on the "OK" button.
- The argument window opens. The task of the standard Standotclone operator. It is the definition of standard deviation during the sample. Its syntax looks like this:
= Standotclonal.V (number1; number2; ...)
It is easy to guess that the "number" argument is the address of the sample element. If the sample is placed in a single array, then you can, using only one argument, give a link to this range.
Install the cursor in the "Number1" field and, as always, holding the left mouse button, we allocate a set. After the coordinates hit the field, do not rush to press the "OK" button, as the result will be incorrect. Former, we need to return to the operator's arguments window trust. Student to make the last argument. For this, click on the appropriate name in the formula row.
- The argument window is replaced again. Install the cursor in the "Size" field. Again, click on the triangle already familiar to us to go to the selection of operators. As you understand, we need the name "Account". Since we used this feature when calculating in the previous method, it is present in this list, so just click on it. If you do not detect it, then act according to the algorithm described in the first method.
- After hitting the argument window, we put the cursor in the "Number1" field and with a clamping mouse button, we highlight a set. Then clay on the "OK" button.
- After that, the program makes the calculation and displays the value of the confidence interval.
- To determine the boundaries, we will again need to calculate the average sample value. But, given the fact that the calculation algorithm with the help of the formula is the same as in the previous method, and even the result has not changed, we will not stop in detail the second time.
- After creating the results of the calculation of the SRVNAH and trust. The detector, we obtain the right border of the confidence interval.
- Related from the results of the calculation of the operator, the result of the calculation of the calculation is trusted. The detector, we have the left border of the confidence interval.
- If the calculation is to write down with one formula, then the calculation of the right border in our case will look like this:
= SRNAVOV (B2: B13) + trust .styudient (0.03; Standotclonal.V (B2: B13); score (B2: B13))
- Accordingly, the formula for calculating the left border will look like this:
= SRNAVOV (B2: B13) - Essential.styudent (0.03; Standotclonal.V (B2: B13); account (B2: B13))
As you can see, Excel's tools will significantly facilitate the calculation of the confidence interval and its boundaries. For these purposes, individual operators are used for samples, in which the dispersion is known and unknown.