Confidence interval is a formula for calculating a physicist. Samples and Confidence Intervals

The calculation of the confidence interval is based on the mean error of the corresponding parameter. Confidence interval shows the limits with probability (1-a) the true value of the estimated parameter is. Here a is the level of significance, (1-a) is also called the confidence level.

In the first chapter, we showed that, for example, for the arithmetic mean, the true population mean in about 95% of cases lies within 2 mean errors of the mean. Thus, the boundaries of the 95% confidence interval for the mean will be separated from the sample mean by twice the mean error of the mean, i.e. we multiply the mean error of the mean by some factor depending on the confidence level. For the mean and the difference of the means, the Student's coefficient (the critical value of the Student's criterion) is taken, for the share and difference of the shares, the critical value of the criterion z. The product of the coefficient by the mean error can be called the marginal error of this parameter, i.e. the maximum that we can get when evaluating it.

Confidence interval for arithmetic mean : .

Here is the sample mean;

Average error of the arithmetic mean;

s - sample standard deviation;

f = n-1 (Student's coefficient).

Confidence interval for difference of arithmetic means :

Here is the difference of the sample means;

- the average error of the difference of the arithmetic means;

s 1, s 2 - sample standard deviations;

n 1, n 2

Critical value of the Student's criterion for a given significance level a and the number of degrees of freedom f = n 1 + n 2-2 (Student's coefficient).

Confidence interval for share :

Here d is the sample rate;

- average share error;

n- sample size (size of the group);

Confidence interval for difference of shares :

Here is the difference of the sample shares;

- the average error of the difference of the arithmetic means;

n 1, n 2- volumes of samples (number of groups);

The critical value of the criterion z at a given level of significance a (,,).

Calculating the confidence intervals for the difference in indicators, we, firstly, directly see the possible values of the effect, and not just its point estimate. Secondly, we can draw a conclusion about the acceptance or refutation of the null hypothesis and, thirdly, we can draw a conclusion about the power of the criterion.

When testing hypotheses using confidence intervals, the following rule must be followed:

If the 100 (1-a) -percent confidence interval of the difference of means does not contain zero, then the differences are statistically significant at the significance level a; on the contrary, if this interval contains zero, then the differences are not statistically significant.

Indeed, if this interval contains zero, then it means that the compared indicator can be either more or less in one of the groups than in the other, i.e. the observed differences are random.

By the place where zero is within the confidence interval, one can judge the power of the criterion. If zero is close to the lower or upper border of the interval, then perhaps with a larger number of compared groups, the differences would reach statistical significance. If zero is close to the middle of the interval, then it means that the increase and decrease in the indicator in the experimental group are equally probable, and, probably, there are really no differences.

Examples:

To compare the operative mortality with the use of two different types of anesthesia: 61 people were operated on with the use of the first type of anesthesia, 8 died, with the use of the second - 67 people, 10 died.

d 1 = 8/61 = 0.131; d 2 = 10/67 = 0.149; d1-d2 = - 0.018.

The difference in lethality of the compared methods will be in the range (-0.018 - 0.122; -0.018 + 0.122) or (-0.14; 0.104) with a probability of 100 (1-a) = 95%. The interval contains zero, i.e. the hypothesis of the same mortality rate for two different types of anesthesia cannot be rejected.

Thus, mortality can and will decrease to 14% and increase to 10.4% with a probability of 95%, i.e. zero is located approximately in the middle of the interval, so it can be argued that, most likely, these two methods do not really differ in lethality.

In the example considered earlier, the average tapping test time was compared in four groups of students differing in exam score. Let us calculate the confidence intervals for the average pressing time for students who passed the exam at 2 and 5 and the confidence interval for the difference between these averages.

We find the Student's coefficients according to the Student's distribution tables (see Appendix): for the first group: = t (0.05; 48) = 2.011; for the second group: = t (0.05; 61) = 2.000. Thus, the confidence intervals for the first group: = (162.19-2.011 * 2.18; 162.19 + 2.011 * 2.18) = (157.8; 166.6), for the second group (156.55- 2.000 * 1.88; 156.55 + 2.000 * 1.88) = (152.8; 160.3). So, for those who passed the exam for 2, the average pressing time lies in the range from 157.8 ms to 166.6 ms with a probability of 95%, for those who passed the exam for 5 - from 152.8 ms to 160.3 ms with a probability of 95%.

You can also test the null hypothesis using confidence intervals for means, and not just for the difference in means. For example, as in our case, if the confidence intervals for the means overlap, then the null hypothesis cannot be rejected. In order to reject a hypothesis at the chosen level of significance, the corresponding confidence intervals should not overlap.

Let us find the confidence interval for the difference in the average pressing time in the groups who passed the exam by 2 and 5. The difference in the mean: 162.19 - 156.55 = 5.64. Student's coefficient: = t (0.05; 49 + 62-2) = t (0.05; 109) = 1.982. Group standard deviations will be equal to:; ... We calculate the average error of the difference between the means:. Confidence interval: = (5.64-1.982 * 2.87; 5.64 + 1.982 * 2.87) = (-0.044; 11.33).

So, the difference in the average pressing time in the groups that passed the exam for 2 and for 5 will be in the range from -0.044 ms to 11.33 ms. This interval includes zero, i.e. the average pressing time for those who passed the exam perfectly may increase and decrease in comparison with those who did not pass the exam satisfactorily, i.e. the null hypothesis cannot be rejected. But zero is very close to the lower border, the pressing time is much more likely to decrease in the case of those who successfully passed it. Thus, we can conclude that there are still differences in the average pressing time between those who passed on 2 and 5, we just could not find them with a given change in the average time, the spread of the average time and sample volumes.

The power of a test is the probability of rejecting an incorrect null hypothesis, i.e. find differences where they really are.

The power of the test is determined based on the level of significance, the magnitude of the differences between the groups, the spread of values in the groups and the size of the samples.

For Student's test and analysis of variance, you can use sensitivity diagrams.

The power of the criterion can be used in the preliminary determination of the required number of groups.

The confidence interval shows the limits with a given probability that the true value of the estimated parameter is.

Confidence intervals can be used to test statistical hypotheses and draw conclusions about the sensitivity of the criteria.

LITERATURE.

Glantz S. - Chapter 6.7.

Rebrova O.Yu. - p. 112-114, p. 171-173, p. 234-238.

Sidorenko E.V. - p. 32-33.

Questions for self-examination of students.

1. What is the cardinality of a test?

2. In what cases is it necessary to evaluate the power of the criteria?

3. Methods for calculating power.

6. How to test a statistical hypothesis using a confidence interval?

7. What can you say about the power of the criterion when calculating the confidence interval?

Tasks.

Often an appraiser has to analyze the real estate market of the segment in which the appraisal object is located. If the market is developed, it can be difficult to analyze the entire set of presented objects, therefore, a sample of objects is used for analysis. This sample does not always turn out to be homogeneous; sometimes it is necessary to clear it of extremes - too high or too low market offers. For this purpose applies confidence interval... The purpose of this study is to carry out a comparative analysis of two methods for calculating the confidence interval and choose the optimal calculation option when working with different samples in the estimatica.pro system.

Confidence interval is an interval of characteristic values calculated on the basis of a sample, which, with a known probability, contains the estimated parameter of the general population.

The meaning of calculating the confidence interval is to construct, based on the sample data, such an interval so that it can be asserted with a given probability that the value of the estimated parameter is in this interval. In other words, the confidence interval contains, with a certain probability, the unknown value of the estimated value. The wider the interval, the higher the inaccuracy.

There are different methods for determining the confidence interval. In this article, we will consider 2 ways:

through the median and standard deviation;
through the critical value of t-statistics (Student's coefficient).

Stages of comparative analysis of different methods for calculating CI:

1. we form a sample of data;

2. we process it by statistical methods: we calculate the mean, median, variance, etc .;

3. we calculate the confidence interval in two ways;

4. we analyze the cleaned samples and the obtained confidence intervals.

Stage 1. Data sampling

The sample was formed using the estimatica.pro system. The sample included 91 offers for the sale of 1-room apartments in the 3rd price zone with the "Khrushchevka" layout type.

Table 1. Initial sample

	Price for 1 sq.m., d.e.

Fig. 1. Initial sample

Stage 2. Processing of the original sample

The processing of a sample by statistical methods requires the calculation of the following values:

1. Arithmetic mean

2. Median - the number characterizing the sample: exactly half of the sample is greater than the median, the other half is less than the median

(for a sample with an odd number of values)

3. Span - the difference between the maximum and minimum values in the sample

4. Variance - used for more accurate estimation of data variation

5. Sample standard deviation (hereinafter - RMS) is the most common indicator of the dispersion of adjustment values around the arithmetic mean.

6. Coefficient of variation - reflects the degree of dispersion of the adjustment values

7.Oscillation coefficient - reflects the relative fluctuation of the extreme values of prices in the sample around the average

Table 2. Statistical indicators of the original sample

The coefficient of variation, which characterizes the homogeneity of the data, is 12.29%, but the coefficient of oscillation is too large. Thus, we can argue that the original sample is not homogeneous, so let's move on to calculating the confidence interval.

Step 3. Calculation of the confidence interval

Method 1. Calculation through the median and standard deviation.

The confidence interval is determined as follows: the minimum value - the standard deviation is subtracted from the median; maximum value - standard deviation is added to the median.

Thus, the confidence interval (CU 47179; CU 60689)

Rice. 2. Values that fall within the confidence interval 1.

Method 2. Construction of the confidence interval through the critical value of the t-statistics (Student's coefficient)

S.V. Gribovsky in his book "Mathematical Methods for Assessing the Value of Property" describes a method for calculating the confidence interval through the Student's coefficient. When calculating by this method, the evaluator himself must set the level of significance ∝, which determines the probability with which the confidence interval will be constructed. Significance levels of 0.1 are commonly used; 0.05 and 0.01. Confidence probabilities of 0.9 correspond to them; 0.95 and 0.99. With this method, the true values of the mathematical expectation and variance are assumed to be practically unknown (which is almost always true when solving practical estimation problems).

Confidence Interval Formula:

n is the sample size;

Critical value of t-statistics (Student's distribution) with a significance level ∝, the number of degrees of freedom n-1, which is determined using special statistical tables or using MS Excel (→ "Statistical" → STYUDRASPOBR);

∝ - the level of significance, we take ∝ = 0.01.

Rice. 2. Values that fall within the confidence interval 2.

Stage 4. Analysis of different methods of calculating the confidence interval

Two methods of calculating the confidence interval - through the median and the Student's coefficient - led to different values of the intervals. Accordingly, we got two different cleaned samples.

Table 3. Statistical indicators for three samples.

Index	Initial sample	Option 1	Option 2
Mean


Dispersion

Coef. variations
Coef. oscillations
Number of retired objects, pcs.

Based on the calculations performed, it can be said that the values of the confidence intervals obtained by different methods intersect, therefore, any of the calculation methods can be used at the discretion of the evaluator.

However, we believe that when working in the estimatica.pro system, it is advisable to choose a method for calculating the confidence interval depending on the degree of market development:

if the market is undeveloped, apply the method of calculation through the median and standard deviation, since the number of retired objects in this case is small;
if the market is developed, apply the calculation through the critical value of the t-statistic (Student's coefficient), since it is possible to form a large initial sample.

In preparing the article, the following were used:

1. Gribovsky S.V., Sivets S.A., Levykina I.A. Mathematical methods for assessing the value of property. Moscow, 2014

2. Data of the estimatica.pro system

Probabilities, recognized as sufficient to confidently judge the general parameters on the basis of sample characteristics, are called confidential .

Typically, 0.95 is chosen as confidence probabilities; 0.99; 0.999 (it is customary to express them as a percentage - 95%, 99%, 99.9%). The higher the measure of responsibility, the higher the level of confidence: 99% or 99.9%.

A confidence level of 0.95 (95%) is considered sufficient in scientific research in the field of physical culture and sports.

The interval in which the sample arithmetic mean of the general population is found with a given confidence level is called confidence interval .

Significance level of assessment- a small number α, the value of which assumes the probability that it falls outside the confidence interval. In accordance with confidence probabilities: α 1 = (1- 0.95) = 0.05; α 2 = (1 - 0.99) = 0.01, etc.

Confidence interval for mean (mathematical expectation) a normal distribution:

where is the reliability (confidence level) of the assessment; - sample mean; s - corrected standard deviation; n is the sample size; t γ is the value determined from the Student's distribution table (see Appendix, Table 1) for given n and γ.

To find the boundaries of the confidence interval for the average value of the general population, it is necessary:

1. Calculate and s.

2. You should set the confidence level (reliability) of the γ estimate 0.95 (95%) or the significance level α 0.05 (5%)

3. Using the table t - Student's distribution (Appendix, Table 1) find the boundary values of t γ.

Since the t-distribution is symmetric about the zero point, it is sufficient to know only the positive value of t. For example, if the sample size is n = 16, then the number of degrees of freedom (degrees of freedom, df) t- distributions df=16 - 1=15 ... According to the table. 1 applications t 0.05 = 2.13 .

4. Find the boundaries of the confidence interval for α = 0.05 and n = 16:

Trust Boundaries:

For large sample sizes (n ≥ 30) t - the Student's distribution becomes normal. Therefore, the confidence interval for for n ≥ 30 can be written as follows:

where u are the percentage points of the normalized normal distribution.

For standard confidence levels (95%, 99%; 99, 9%) and significance levels α values ( u) are given in Table 8.

Table 8

Values for standard confidence levels α

α	u
0,05	1,96
0,01	2,58
0,001	3,28

Based on the data of example 1, we define the boundaries of the 95% confidence interval (α = 0.05) for the average result of a standing jump. In our example, the sample size is n = 65, then recommendations for a large sample size can be used to determine the boundaries of the confidence interval.

There are two types of estimates in statistics: point and interval. Point estimate is a separate sample statistic that is used to estimate a parameter of a population. For example, the sample mean is a point estimate of the mathematical expectation of the general population, and the sample variance S 2- point estimate of the variance of the general population σ 2... it was shown that the sample mean is an unbiased estimate of the mathematical expectation of the general population. The sample mean is called unbiased because the mean of all sample means (for the same sample size n) is equal to the mathematical expectation of the general population.

In order for the sample variance S 2 became an unbiased estimate of the population variance σ 2, the denominator of the sample variance should be set equal to n – 1 , but not n... In other words, the variance of the general population is the average of all possible sample variances.

When assessing the parameters of the general population, it should be borne in mind that sample statistics, such as , depend on specific samples. To take this fact into account, to obtain interval estimation the mathematical expectation of the general population, the distribution of sample means is analyzed (for more details, see). The constructed interval is characterized by a certain confidence level, which is the probability that the true parameter of the general population is estimated correctly. Similar confidence intervals can be used to estimate the share of a feature R and the main distributed mass of the general population.

Download a note in format or, examples in format

Construction of the confidence interval for the mathematical expectation of the general population with a known standard deviation

Construction of a confidence interval for the share of a feature in the general population

In this section, the concept of a confidence interval is extended to categorical data. This allows you to estimate the share of the trait in the general population. R using a sample rate RS= X /n... As indicated, if the quantities nR and n(1 - p) exceed the number 5, the binomial distribution can be approximated by a normal one. Therefore, to assess the share of a feature in the general population R an interval can be plotted, the confidence level of which is (1 - α) х100%.

where pS- a selective share of a feature equal to NS/n, i.e. the number of successes divided by the sample size, R- the share of the feature in the general population, Z- the critical value of the standardized normal distribution, n- sample size.

Example 3. Suppose that a sample is retrieved from the information system, consisting of 100 invoices completed during the last month. Let's say that 10 of these invoices are made with errors. Thus, R= 10/100 = 0.1. The 95% confidence level corresponds to the critical value Z = 1.96.

Thus, the probability that between 4.12% and 15.88% of invoices contains errors is 95%.

For a given sample size, the confidence interval containing the share of a feature in the general population seems to be wider than for a continuous random variable. This is because measurements of a continuous random variable contain more information than measurements of categorical data. In other words, categorical data that take only two values contain insufficient information to estimate the parameters of their distribution.

Vcalculating estimates derived from a finite population

Estimation of the mathematical expectation. The correction factor for the final population ( fpc) was used to decrease the standard error by a factor. When calculating confidence intervals for estimates of population parameters, a correction factor is applied in situations where samples are retrieved without being returned. Thus, the confidence interval for the mathematical expectation having a confidence level equal to (1 - α) х100%, is calculated by the formula:

Example 4. To illustrate the application of the correction factor for the final population, let us return to the problem of calculating the confidence interval for the average amount of invoices discussed above in Example 3. Suppose that a company issues 5,000 invoices per month, and X̅= 110.27 dollars, S= $ 28.95 N = 5000, n = 100, α = 0.05, t 99 = 1.9842. By formula (6) we get:

Assessment of the share of the feature. When choosing without returning, the confidence interval for the fraction of a feature having a confidence level equal to (1 - α) х100%, is calculated by the formula:

Confidence Intervals and Ethical Issues

Ethical problems often arise when sampling the population and formulating statistical conclusions. The main one is how confidence intervals and point estimates of sample statistics agree. Publication of point estimates without appropriate confidence intervals (usually 95% confidence levels) and sample sizes from which they are derived can be misleading. This can give the user the impression that the point estimate is exactly what he needs to predict the properties of the entire population. Thus, it is necessary to understand that in any research the focus should be not on the point, but on the interval estimates. In addition, special attention should be paid to the correct selection of sample sizes.

Most often, the objects of statistical manipulation are the results of sociological surveys of the population on various political issues. At the same time, the results of the survey are put on the front pages of newspapers, and the error of the sample research and the methodology of statistical analysis are printed somewhere in the middle. To prove the validity of the obtained point estimates, it is necessary to indicate the size of the sample on the basis of which they were obtained, the boundaries of the confidence interval and its level of significance.

Next note

Used materials of the book Levin and other Statistics for managers. - M .: Williams, 2004 .-- p. 448-462

Central limit theorem states that with a sufficiently large sample size, the sample distribution of means can be approximated by a normal distribution. This property does not depend on the type of distribution of the general population.

One of the methods for solving statistical problems is to calculate the confidence interval. It is used as a preferred alternative to point estimation in small sample sizes. It should be noted that the process of calculating the confidence interval itself is rather complicated. But the tools of the Excel program allow you to simplify it somewhat. Let's find out how this is done in practice.

This method is used for interval estimation of various statistical quantities. The main task of this calculation is to get rid of the uncertainties of the point estimate.

In Excel, there are two main options for performing calculations using this method: when the variance is known, and when it is unknown. In the first case, the function is used for calculations TRUST.NORM, and in the second - CONFIDENCE STUDENT.

Method 1: function CONFIDENCE NORMAL

Operator TRUST.NORM, which belongs to the statistical group of functions, first appeared in Excel 2010. Earlier versions of this program use its counterpart TRUST... The purpose of this operator is to calculate the normally distributed confidence interval for the average population.

Its syntax is as follows:

TRUST.NORM (alpha; standard_dev; size)

"Alpha"- an argument indicating the level of significance that is used to calculate the confidence level. The confidence level is equal to the following expression:

(1- "Alpha") * 100

"Standard deviation" Is an argument, the essence of which is clear from the name. This is the standard deviation of the proposed sample.

"The size"- an argument defining the sample size.

All arguments to this operator are required.

Function TRUST has exactly the same arguments and possibilities as the previous one. Its syntax is as follows:

TRUST (alpha; standard_dev; size)

As you can see, the differences are only in the name of the operator. The specified function is retained in Excel 2010 and newer in a special category for compatibility reasons. "Compatibility"... In versions of Excel 2007 and earlier, it is present in the main group of statistical operators.

The boundary of the confidence interval is determined using a formula of the following form:

X + (-) TRUST.NORMAL

Where X Is the mean of the sampled value, which is located in the middle of the selected range.

Now let's look at how to calculate the confidence interval using a specific example. 12 tests were carried out, as a result of which various results were obtained, which are listed in the table. This is our totality. The standard deviation is 8. We need to calculate the confidence interval at a 97% confidence level.

Select the cell where the data processing result will be displayed. Click on the button "Insert function".

Appears Function wizard... Go to the category "Statistical" and highlight the name "TRUST.NORM"... After that, click on the button "OK".

The arguments window opens. Its fields naturally correspond to the names of the arguments.
We set the cursor to the first field - "Alpha"... Here we should indicate the level of significance. As we remember, our level of trust is 97%. At the same time, we said that it is calculated in this way:
(1-level of trust) / 100

That is, substituting the value, we get:

By simple calculations, we find out that the argument "Alpha" is equal to 0,03 ... Enter this value in the field.

As you know, by condition, the standard deviation is 8 ... Therefore, in the field "Standard deviation" just write down this number.

In field "The size" you need to enter the number of elements of the tests performed. As we remember, their 12 ... But in order to automate the formula and not edit it every time a new test is carried out, let's set this value not with an ordinary number, but using the operator CHECK... So, we place the cursor in the field "The size", and then click on the triangle, which is located to the left of the formula bar.

A list of recently used functions appears. If the operator CHECK has been used recently, it should be on this list. In this case, you just need to click on its name. Otherwise, if you do not find it, then go to item "Other functions ...".

The already familiar to us appears Function wizard... Move to the group again "Statistical"... Highlight the name there "CHECK"... Click on the button "OK".

The argument window for the above operator appears. This function is designed to calculate the number of cells in the specified range that contain numeric values. Its syntax is as follows:
COUNT (value1; value2; ...)

Argument group "Values" is a reference to the range in which you want to calculate the number of cells filled with numeric data. There can be up to 255 such arguments in total, but in our case we only need one.

Place the cursor in the field "Value1" and, holding down the left mouse button, select on the sheet the range that contains our collection. Then its address will be displayed in the field. Click on the button "OK".

After that, the application will perform the calculation and display the result in the cell where it is. In our particular case, the formula turned out to be like this:
CONFIDENT.NORM (0,03; 8; COUNT (B2: B13))

The total calculation result was 5,011609 .

But that is not all. As we remember, the boundary of the confidence interval is calculated by adding and subtracting from the mean of the sampled value of the calculation result TRUST.NORM... In this way, the right and left boundaries of the confidence interval are calculated, respectively. The sample mean itself can be calculated using the operator AVERAGE.
This operator is designed to calculate the arithmetic mean of the selected range of numbers. It has the following rather simple syntax:

AVERAGE (number1; number2; ...)

Argument "Number" can be either a single numeric value or a reference to cells or even entire ranges that contain them.

So, select the cell in which the calculation of the average value will be displayed, and click on the button "Insert function".

Opens Function wizard... Go to the category again "Statistical" and select the name from the list "AVERAGE"... As always, click on the button "OK".

The arguments window starts. Place the cursor in the field "Number1" and with the left mouse button pressed, select the entire range of values. After the coordinates are displayed in the field, click on the button "OK".

After that AVERAGE outputs the result of the calculation to a sheet element.

We calculate the right border of the confidence interval. To do this, select a separate cell, put the sign «=» and add the contents of the sheet elements in which the results of function calculations are located AVERAGE and TRUST.NORM... In order to perform the calculation, press the button Enter... In our case, we got the following formula:
Calculation result: 6,953276

In the same way, we calculate the left border of the confidence interval, only this time from the result of the calculation AVERAGE subtract the result of calculating the operator TRUST.NORM... It turns out the formula for our example is of the following type:
Calculation result: -3,06994

We tried to describe in detail all the steps for calculating the confidence interval, so we described each formula in detail. But you can combine all the actions in one formula. The calculation of the right border of the confidence interval can be written as follows:
AVERAGE (B2: B13) + CONFIDENTIAL NORM (0.03; 8; COUNT (B2: B13))

A similar calculation of the left border would look like this:
AVERAGE (B2: B13) -TRUST.NORM (0.03; 8; COUNT (B2: B13))

Method 2: CONFIDENCE STUDENT function

In addition, Excel has one more function that is related to the calculation of the confidence interval - CONFIDENCE STUDENT... It has only appeared since Excel 2010. This operator calculates the confidence interval of the population using the Student's t distribution. It is very convenient to use when the variance and, accordingly, the standard deviation are unknown. The syntax of the operator is as follows:

TRUST.STUDENT (alpha; standard_dev; size)

As you can see, the names of the operators remained unchanged in this case.

Let's see how to calculate the boundaries of the confidence interval with an unknown standard deviation using the example of the same population that we considered in the previous method. The level of trust, like last time, is 97%.

Select the cell in which the calculation will be made. Click on the button "Insert function".

In the opened Function wizard go to the category "Statistical"... Choosing a name "CONFIDENCE STUDENT"... Click on the button "OK".

The argument window for the specified operator is launched.
In field "Alpha", given that the confidence level is 97%, we write down the number 0,03 ... We will not dwell on the principles of calculating this parameter for the second time.

After that, we place the cursor in the field "Standard deviation"... This time, this indicator is unknown to us and we need to calculate it. This is done using a special function - STDEV.B... To open the window of this operator, click on the triangle to the left of the formula bar. If we do not find the desired name in the list that opens, then go to item "Other functions ...".

Starts up Function wizard... Moving to the category "Statistical" and mark the name in it "STDEV.V"... Then we click on the button "OK".

The arguments window opens. The operator's task STDEV.B is the definition of the standard deviation of the sample. Its syntax looks like this:
STDEV.B (number1; number2; ...)

It is not hard to guess that the argument "Number" Is the address of the sample item. If the selection is placed in a single array, then using only one argument, you can give a reference to this range.

Place the cursor in the field "Number1" and, as always, holding down the left mouse button, select the population. After the coordinates have entered the field, do not rush to press the button "OK", since the result will be incorrect. First, we need to return to the operator arguments window CONFIDENCE STUDENT to make the last argument. To do this, click on the appropriate name in the formula bar.

The argument window for the familiar function opens again. Place the cursor in the field "The size"... Again, click on the already familiar triangle to go to the choice of operators. As you understand, we need a name "CHECK"... Since we used this function in the calculations in the previous method, it is present in this list, so just click on it. If you do not find it, then proceed according to the algorithm described in the first method.

Once in the arguments window CHECK, put the cursor in the field "Number1" and with the mouse button held down, select the population. Then we click on the button "OK".

After that, the program calculates and displays the value of the confidence interval.

To define the boundaries, we will again need to calculate the average of the sample. But, given that the calculation algorithm using the formula AVERAGE the same as in the previous method, and even the result has not changed, we will not dwell on this in detail the second time.

By adding the results of the calculation AVERAGE and CONFIDENCE STUDENT, we get the right border of the confidence interval.

Subtracting from the results of the calculation of the operator AVERAGE calculation result CONFIDENCE STUDENT, we have the left border of the confidence interval.

If the calculation is written in one formula, then the calculation of the right border in our case will look like this:
AVERAGE (B2: B13) + TRUSTED STUDENT (0.03; STDEV.B (B2: B13); COUNT (B2: B13))

Accordingly, the formula for calculating the left border will look like this:
AVERAGE (B2: B13) - TRUSTED STUDENT (0.03; STDEV.B (B2: B13); COUNT (B2: B13))

As you can see, Excel tools make it much easier to calculate the confidence interval and its boundaries. For these purposes, separate operators are used for samples for which the variance is known and unknown.