We find the critical value of the t criterion using the table. Student coefficients

The method allows you to test the hypothesis that the average values ​​of two general populations from which the compared ones are extracted dependent selections differ from each other. The assumption of dependence most often means that the characteristic is measured on the same sample twice, for example, before the intervention and after it. In the general case, each representative of one sample is assigned a representative from another sample (they are combined in pairs) so that the two data series are positively correlated with each other. Weaker types of sample dependence: sample 1 - husbands, sample 2 - their wives; sample 1 - one-year-old children, sample 2 is made up of twins of children in sample 1, etc.

Testable statistical hypothesis, as in the previous case, H 0: M 1 = M 2(mean values ​​in samples 1 and 2 are equal). If it is rejected, the alternative hypothesis is accepted that M 1 more (less) M 2.

Initial assumptions for statistical testing:

Each representative of one sample (from one general population) is associated with a representative of another sample (from another general population);

The data from the two samples are positively correlated (form pairs);

The distribution of the studied characteristic in both samples corresponds to the normal law.

Source data structure: there are two values ​​of the studied feature for each object (for each pair).

Restrictions: the distribution of the characteristic in both samples should not differ significantly from normal; the data of two measurements corresponding to one and the other sample are positively correlated.

Alternatives: Wilcoxon T test, if the distribution for at least one sample differs significantly from normal; t-Student test for independent samples - if the data for two samples do not correlate positively.

Formula for the empirical value of the Student's t test reflects the fact that the unit of analysis for differences is difference (shift) attribute values ​​for each pair of observations. Accordingly, for each of the N pairs of attribute values, the difference is first calculated d i = x 1 i - x 2 i.

where M d is the average difference of values; σ d - standard deviation of differences.

Calculation example:

Let’s assume that during testing the effectiveness of the training, each of the 8 members of the group was asked the question “How often does your opinion coincide with the opinion of the group?” - twice, before and after the training. A 10-point scale was used for responses: 1 - never, 5 - half the time, 10 - always. The hypothesis was tested that as a result of the training, the self-esteem of conformity (the desire to be like others in the group) of the participants would increase (α = 0.05). Let's create a table for intermediate calculations (Table 3).

Table 3

The arithmetic mean for the difference M d = (-6)/8 = -0.75. Subtract this value from each d (the penultimate column of the table).

The formula for standard deviation differs only in that d appears in it instead of X. We substitute all the necessary values, we get:

σ d = = 0.886.

Step 1. Calculate the empirical value of the criterion using formula (3): average difference Md= -0.75; standard deviation σ d = 0,886; t e = 2,39; df = 7.

Step 2. Using the table of critical values ​​of the t-Student criterion, we determine the p-level of significance. For df = 7 the empirical value is between the critical values ​​for r= 0.05 and p — 0.01. Hence, r< 0,05.

Step 3. We make a statistical decision and formulate a conclusion. The statistical hypothesis of equality of average values ​​is rejected. Conclusion: the indicator of self-assessment of participants’ conformity after the training increased statistically significantly (at significance level p< 0,05).

Parametric methods include comparison of variances of two samples according to the criterion F-Fisher. Sometimes this method leads to valuable meaningful conclusions, and in the case of comparing means for independent samples, comparing variances is mandatory procedure.

To calculate F em you need to find the ratio of the variances of the two samples, and so that the larger variance is in the numerator, and the smaller one is in the denominator.

Comparison of Variances. The method allows you to test the hypothesis that the variances of the two general populations from which the compared samples are drawn differ from each other. Tested statistical hypothesis H 0: σ 1 2 = σ 2 2 (the variance in sample 1 is equal to the variance in sample 2). If it is rejected, the alternative hypothesis is accepted that one variance is greater than the other.

Initial assumptions: two samples are drawn randomly from different populations with a normal distribution of the characteristic being studied.

Source data structure: the characteristic being studied is measured in objects (subjects), each of which belongs to one of the two samples being compared.

Restrictions: the distributions of the trait in both samples do not differ significantly from normal.

Alternative method: Levene's test, the use of which does not require checking the assumption of normality (used in the SPSS program).

Formula for the empirical value of the Fisher's F test:

(4)

where σ 1 2 — large dispersion, and σ 2 2 - smaller dispersion. Since it is not known in advance which dispersion is greater, then to determine the p-level it is used Table of critical values ​​for non-directional alternatives. If F e > F Kp for the corresponding number of degrees of freedom, then r< 0,05 и статистическую гипотезу о равенстве дисперсий можно отклонить (для α = 0,05).

Calculation example:

The children were given regular arithmetic problems, after which one randomly selected half of the students were told that they had failed the test, and the rest were told the opposite. Each child was then asked how many seconds it would take them to solve a similar problem. The experimenter calculated the difference between the time the child called and the result of the completed task (in seconds). It was expected that the message of failure would cause some inadequacy in the child's self-esteem. The hypothesis tested (at the α = 0.005 level) was that the variance of the aggregate self-esteem does not depend on reports of success or failure (H 0: σ 1 2 = σ 2 2).

The following data was obtained:

Step 1. Calculate the empirical value of the criterion and the number of degrees of freedom using formulas (4):

Step 2. According to the table of critical values ​​of the Fisher f-criterion for non-directional alternatives we find the critical value for df number= 11; df know= 11. However, there is a critical value only for df number= 10 and df know = 12. A larger number of degrees of freedom cannot be taken, so we take the critical value for df number= 10: For r= 0,05 F Kp = 3.526; For r= 0,01 F Kp = 5,418.

Step 3. Making a statistical decision and meaningful conclusion. Since the empirical value exceeds the critical value for r= 0.01 (and even more so for p = 0.05), then in this case p< 0,01 и принимается альтернативная гипо-теза: дисперсия в группе 1 превышает дисперсию в группе 2 (p< 0.01). Consequently, after a message about failure, the inadequacy of self-esteem is higher than after a message about success.

MINISTRY OF EDUCATION OF THE RUSSIAN FEDERATION

Perm State University

Scientific and educational center

“Nonequilibrium transitions in continuous media”

Yu.K. Bratukhin, G.F. Putin

PROCESSING OF EXPERIMENTAL DATA

Textbook for laboratory workshop “Mechanics”

general physics course

Perm 2003

BBK 22.253.3

UDC 531.7.08 (076.5)

Bratukhin Yu.K., Putin G.F.

B 87 Processing of experimental data: Textbook for the laboratory workshop “Mechanics” of the general physics course / Perm. univ. – Perm, 2003. – 80 p.

ISBN 5–7944–0370 5

The manual is intended for first-year students of physics departments of universities, as well as students of other natural science departments of universities and technical universities who are starting to work in a workshop in general physics. It is compiled in accordance with the current syllabus of the general physics course as an introduction to the course of laboratory work. A brief summary of the theory relevant to all tasks is given, and a description of several laboratory works, each of which can be performed simultaneously by students of the entire group. The formulation of the tasks ensures that the implementation of most experimental installations is simple and that students, having completed the experiments, themselves could propose their improvement or, if desired, reproduce them at home. Therefore, the manual can also be used for independent work.

Table 10. Ill. 13. Bibliography 12 titles

The textbook was prepared with the support of the Scientific and Educational Center “Nonequilibrium Transitions in Continuum Media”

Published by decision of the Academic Council of the Faculty of Physics of Perm University

Reviewers:

Department of Applied Physics, Perm State Technical University;

Doctor of Physical and Mathematical Sciences, Professor A.F. Pshenichnikov

ISBN 5–7944–0370 5 Ó Y.K.Bratukhin, G.F.Putin, 2003

1. Rules for processing measurement results. . . . . . .5

1.1. Processing of direct measurement results. . . . . . . . . . . . . . . 5

1.2. Processing the results of indirect measurements. . . . . . . . . . . . .9

2. Preparation of reports on laboratory work. . 11

3. Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12

4. Types of measurements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15

4.1. Measurement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15

4.2. Direct measurements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15

4.3. Indirect measurements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

5. Presentation of measurement results. . . . . . . . . . 16

5.1. Recording the measurement result. . . . . . . . . . . . . . . . . . . . . . . . . .16

5.2. Average value. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .16

5.3. True meaning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .17

5.4. Confidence interval. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

5.5. Reliability factor. . . . . . . . . . . . . . . . . . . . . . . . . . . . .17

6. Types of errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .18

6.1. Absolute error. . . . . . . . . . . . . . . . . . . . . . . . . . . . .18

6.2. Relative error. . . . . . . . . . . . . . . . . . . . . . . . . . .18

6.3. Systematic error. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

6.4. Random error. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21

6.5. Miss. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

7. Errors of measuring instruments. . . . . . . . . . 23

7.1. Maximum error of the device. . . . . . . . . . . . . . . . . . . . . . . . . . 23

7.2. Accuracy class. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24

7.3. Device error. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24

7.4. Rounding error. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .25

7.5. Total measurement error. . . . . . . . . . . . . . . . . . . . . . . . .25

8. Statistical processing of results

measurements containing random error. . . .27

8.1.Processing the results of direct measurements. . . . . . . . . . . . . . .27

8.2. Gaussian distribution. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30

8.3. Student's method. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .31

8.4. Processing the results of indirect measurements. . . . . . . . . . . .33

9. Approximate calculations during processing

experimental data. . . . . . . . . . . . . . . . . . . . . .37

9.1. The number of significant figures in determining the error. . . . . 38

9.2. Toward the calculation of the total measurement error. . . . . . . . . . . . 40

9.3. On the accuracy of calculations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40

10. Laboratory work on statistical

processing of measurement results. . . . . . . . . . . . . . . .42

10.1. Laboratory work. Studying the distribution of random

quantities. Lorentz gas. . . . . . . . . . 44

10.2. Laboratory work. Experimental determination

numbers π. Buffon's needle. . . . . . . . . . 55

10.3. Laboratory work. Measurement simulation,

accompanied by a large random error. . . . . . . . 64

10.4. Laboratory work. Example of error estimation

indirect measurements. Determination of the density of a solid. . . . . . . . . 70

10.5. Laboratory work. Determination of solid density

bodies of regular geometric shape. . . . . . . . . . . . . . . . . . . . . . . . 76

11. How to write laboratory reports and

research work and

scientific articles. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77

BIBLIOGRAPHICAL LIST. . . . . . . . . . . . . . . . . . . . . . . 79

Chapters 1 and 2 briefly describe the sequence of steps required when processing and presenting experimental data and when preparing reports on laboratory work. A detailed presentation of these issues is contained in sections 3 – 11, which form the main content of this manual.

1. RULES FOR PROCESSING MEASUREMENT RESULTS

When processing measurement results, the following procedure is proposed.

1.1. Processing the results direct measurements

Direct measurements are those in which the desired value is read directly from the device.

Let it be done under the same conditions n measurements of some physical quantity x.

1. We write down the results of each of the individual measurements in a table in a notebook. x 1, x 2, ... x n.

2. Calculate arithmetic mean <x> from n measurements

4. Determine from the table 1.1.1 Student's coefficient t p , n for the number of measurements taken n(and given reliability p = 0.95).

Table 1.1.1

Student's coefficients

p = 0.95

6. Calculate the absolute instrument error D pr according to the formula

Where ω – the price of the smallest division of the device.

Instrument errors ∆ pr and rounding ∆ okr for some instruments used in laboratory workshops on mechanics are indicated in the table 1.1.2 :

Table 1.1.2

Instrument errors

p = 0.95

8. Determine the total absolute error D x experience according to the formula

.	(1.1.6) / (7.5.1)

When calculating D x according to the formula (1.1.6) you can discard one or two of the errors D pr and ∆ okr, if their values ​​are half or significantly less than the remaining ones.

9. Round the absolute error D x(see paragraph 9.1):

Dx = 0. 523 0.5 ;

D x = 0. 124 0.12 .

Here and in some of the following examples, significant figures are underlined.

10. Write down the final experiment result in the form

and indicate the units of measurement.

Record (1.1.7) means that the true value X measured quantity x lies in confidence interval ( - D x, <x>+D x) with probability p, amounting to 95%.

11. Round the average value<x> in such a way that error D x accounted for (see paragraph 9.1):

· to the last category of secondary<x> if D x written with one significant figure

· for the last two categories of average<x> if D x written with two significant figures

12. Define relative error D x rel result of a series of measurements

13. We write down the theoretical, or tabular, or obtained in other studies, etc., value of the physical quantity we are studying x. We provide a detailed link to the cited source.

For example: Table value of aluminum density at a temperature of 20° C

ρ = 2.69 g/cm3.

See: Tables of physical quantities: Handbook / Ed. I.K. Kikoina. M.: Atomizdat. 1976. 1006 p. (table on page 121).

14. We compare the result obtained in our experiments with the data of the previous paragraph 13. If these results differ significantly, the reasons for such a discrepancy should be established: check the calculations; repeat measurements for one or two characteristic parameter values.

15. Write down the output.

For example: Within the experimental error, the results of our measurements agree (disagree) with the theoretical, or tabulated, or given in the cited work [N] value. (The discrepancy in results may be due to the following reasons: ..., or the following shortcomings of the instruments used and the experimental technique: ...).

1.2. Processing the results indirect measurements

Indirect measurements are those in which the quantity of interest to us z is a function k (k 1) directly measured quantities x 1,x 2,…, x k:

When processing the results of indirect measurements, the following method is most common.

1. Data from direct measurements of each parameter x 1, x 2,…, x k processed as described in paragraph 1.1:

· We calculate arithmetic averages arguments , , …, according to the formula (1.1.1) ;

· We find absolute errors D x 1, D x2,…, D x k measurements of each argument using the above formulas (1.1.3) – (1.1.6) . In this case, we set the same reliability value for all arguments p = 0.95.

2. Result of indirect measurement determine by substituting the found averages , , …, from directly measured values ​​into the formula for the function z

where are the partial derivatives of the function z, calculated at the values ​​of the variables x 1 = , x 2 = , …, x k = .

Resulting error D z has the same reliability p = 0.95.

When calculating the resulting error using the formula (1.2.3) those of the terms in the radical expression that are at least half as large as the remaining terms should be neglected.

Another method of processing the results of indirect measurements is described further in paragraph 8.4.

2. PREPARATION OF LABORATORY WORK REPORTS

1. Each work must begin on a new page.

2. The title of the work must be highlighted.

3. After the title, you must write a short Introduction, which should reflect the following points:

· statement of the problem, what phenomenon or what dependence will be investigated, what is expected to be obtained during the work;

· physical quantities that will be measured at work; what are their dimensions and units of measurement;

· description of the measurement method used in the work. In this case, it is imperative to schematically draw the experimental setup and write the working formula and formulas for calculating errors.

4. Experimental results should be written down only in a workbook, in pre-prepared tables. Drafts should not be used for these purposes.

5. If the measured quantity depends on external conditions, for example, on temperature or pressure, it is necessary to write down the experimental conditions.

6. The final result should be recorded at the end of the report, indicating the confidence interval, reliability coefficient, units of measurement and external conditions. This result should be highlighted.

7. If possible, the obtained result must be compared with existing tabular data, theoretical calculations or experimental results of other authors, making sure to provide a link to the source of this data.

8. If the measurements contain systematic errors (for example, the friction force not taken into account in the formulas), then it makes no sense to indicate a confidence interval. In this case, we are limited to assessing the accuracy of the measurement method.

9. To characterize the quality of the results and the experimental method used, it is recommended to always evaluate the relative error of the result.

10. All entries in the notebook must be dated.

INTRODUCTION

The main objectives of laboratory practice are:

· familiarization with devices;

· gaining experience in conducting experiments;

· illustration of theoretical principles of physics.

Obviously, no course of practical work can include all the theory and introduce all the instruments. Therefore, the main task of this workshop is to learn:

· plan the experiment so that the accuracy of the measurements meets the goals;

· take into account the possibility of systematic errors and take measures to eliminate them;

· analyze the results of the experiment and draw the right conclusions;

· evaluate the accuracy of the final result;

· keep records of measurements and calculations neatly, clearly and concisely.

We recommend reading the book “Practical Physics” by J. Squires to get acquainted with the techniques of practical measurements, statistical processing of their results, methods of experimental research and instructions for formatting results, drawing up reports and writing scientific articles.

The proposed laboratory workshop on mechanics as one of the branches of physics is intended not so much to provide the reader with new information - this has already been done by the school - but to help him better understand the essence of more or less known facts and their interrelation. This main goal of ours is also directly related to the cultivation of creative abilities and the formation of independent thinking. Such education can be formed in the following main areas: the ability to generalize - induction; the ability to apply theory to a specific problem - deduction and, perhaps most importantly, the ability to identify contradictions between theoretical generalizations and practice - dialectics.

The theoretical picture that is presented to you in lectures examines those aspects of the real world that the theory considers important. It may turn out that your acquaintance with the natural world is limited only to these aspects, and you will be sure that this is the whole real world, and not its individual facets. In addition, in such a picture everything is so well connected that it is easy to lose sight of the effort that was required to create it. The best cure for such a disease is to go to the laboratory and see the complexity of the real world.

When you study experimental physics, you first of all learn how difficult it can be to test a theory, to measure what you need and not something else, and learn to overcome such difficulties. At the same time, you will gain a perspective on physics in general and on the relationship between theory and experiment.

To teach how to write reports on scientific research (for you, this training is divided into stages - laboratory work, student scientific seminars and conferences, participation in department research), part of the following descriptions of laboratory work are compiled in the style of articles in scientific journals. How to write scientific articles is discussed in detail in the books, where practical advice, recommendations and samples are provided. We will only indicate here that in such descriptions we will adhere to the generally accepted division of the article into the following sections:

· introduction with problem statement;

· description of the experimental setup and measurement technique;

· experimental results;

· their analysis and comparison with the results of other authors;

· conclusions.

For all physicists in the world, this manner of presentation has become such an integral professional skill that it often serves as a reason for jokes and parodies - see, for example, the articles by P. Jordan and R. de Kronig “Movement of the lower jaw in cattle during the process of chewing food” and I . I. Frenkel “Towards a quantum theory of dance” in the book. The authors of this publication could not resist making a similar joke at the expense of cliches and at themselves, placing in the “Discussion of the results” section of the joint publication in a respected academic journal a verbatim quote from the parody “Instructions for the Reader of Scientific Articles”: “If we take into account the approximations made in the analysis , the agreement between the experimental and theoretical results should be considered satisfactory,” but, however, omitting the secret meaning of this phrase revealed in the “Instructions ...”: “There is no agreement at all” - in the confidence that the initiates will understand this meaning without additional explanations.

In order to demonstrate how useful it is, when reporting experimental data, to indicate not only the average characteristics, but also the confidence intervals within which the true values ​​of the measured quantities are most likely to be found, and also to show how theoretical and experimental results can be correlated when studying specific problems, Here are two graphs from the mentioned article.

4. TYPES OF MEASUREMENTS

Measurement

The measurement of any physical quantity is an operation that allows you to find out how many times the measured quantity is greater (or less) than the corresponding value taken as a unit.

It must be emphasized that such a comparison with a standard - measurement - must be carried out under strictly defined conditions and in a very specific way. For example, measuring the length of an object assumes that the standard is motionless in relation to it, and measuring the duration of an event is carried out using a motionless clock. In this sense, Einstein’s analysis of the concept of simultaneity, which in classical physics was not defined at all as a priori"obvious".

Measurements are divided into direct and indirect.

Direct measurements

Direct measurements are those in which the desired value is compared with a unit of measurement directly or using a measuring device calibrated in the appropriate units. Examples of direct measurements are measuring length with a ruler or caliper; measuring masses on lever scales using a set of weights; measuring periods of time using a clock or stopwatch, measuring temperature with a thermometer, voltage with a voltmeter, etc. The value of the measured quantity is measured on the scale of the device or determined by counting measures, weights, etc.

Indirect measurements

Indirect measurements are those in which the desired quantity is found as a function of several directly measured quantities. Examples of indirect measurements include: finding the density of a solid by measuring its mass and volume; measuring the viscosity of a liquid by its volumetric flow rate when flowing through a circular capillary, the length and cross-section of this capillary; or by the speed at which a small ball falls in this liquid, its density and diameter, etc.

Throughout the example, we will use fictitious information so that the reader can make the necessary transformations on his own.

So, let’s say, in the course of research, we studied the effect of drug A on the content of substance B (in mmol/g) in tissue C and the concentration of substance D in the blood (in mmol/l) in patients divided according to some criterion E into 3 groups of equal volume (n = 10). The results of such a fictitious study are shown in the table:

We would like to warn you that we consider samples of size 10 for ease of data presentation and calculations; in practice, such a sample size is usually not enough to form a statistical conclusion.

As an example, consider the data in the 1st column of the table.

Descriptive Statistics

Sample mean

The arithmetic mean, often simply called the "mean", is obtained by adding all the values ​​and dividing that sum by the number of values ​​in the set. This can be shown using an algebraic formula. A set of n observations of a variable x can be represented as x 1 , x 2 , x 3 , ..., x n

The formula for determining the arithmetic mean of observations (pronounced “X with a line”):

= (X 1 + X 2 + ... + X n) / n

= (12 + 13 + 14 + 15 + 14 + 13 + 13 + 10 + 11 + 16) / 10 = 13,1;

Sample variance

One way to measure the dispersion of data is to determine the degree to which each observation deviates from the arithmetic mean. Obviously, the greater the deviation, the greater the variability, variability of observations. However, we cannot use the average of these deviations as a measure of dispersion, because positive deviations compensate for negative deviations (their sum is zero). To solve this problem, we square each deviation and find the average of the squared deviations; this quantity is called variation, or dispersion. Let's take n observations x 1, x 2, x 3, ..., x n, average which is equal to. Calculating the variance this, usually referred to ass2,these observations:

The sample variance of this indicator is s 2 = 3.2.

Standard deviation

Standard (mean square) deviation is the positive square root of the variance. Using n observations as an example, it looks like this:

We can think of standard deviation as a kind of average deviation of observations from the mean. It is calculated in the same units (dimensions) as the original data.

s = sqrt (s 2) = sqrt (3,2) = 1.79.

Coefficient of variation

If you divide the standard deviation by the arithmetic mean and express the result as a percentage, you get the coefficient of variation.

CV = (1.79 / 13.1) * 100% = 13.7

Sample mean error

1.79/sqrt(10) = 0.57;

Student's t coefficient (one-sample t-test)

Used to test the hypothesis about the difference between the average value and some known value m

The number of degrees of freedom is calculated as f=n-1.

In this case, the confidence interval for the mean is between the boundaries of 11.87 and 14.39.

For the 95% confidence level m=11.87 or m=14.39, that is= |13.1-11.82| = |13.1-14.38| = 1.28

Accordingly, in this case, for the number of degrees of freedom f = 10 - 1 = 9 and the 95% confidence level t = 2.26.

Dialog Basic Statistics and Tables

In the module Basic statistics and tables let's choose Descriptive Statistics.

A dialog box will open Descriptive Statistics.

In the field Variables let's choose Group 1.

Clicking on OK, we obtain tables of results with descriptive statistics of the selected variables.

A dialog box will open One-sample t-test.

Suppose we know that the average content of substance B in tissue C is 11.

The table of results with descriptive statistics and Student's t-test is as follows:

We had to reject the hypothesis that the average content of substance B in tissue C is 11.

Since the calculated value of the criterion is greater than the tabulated value (2.26), the null hypothesis is rejected at the selected significance level, and the differences between the sample and the known value are considered statistically significant. Thus, the conclusion about the existence of differences made using the Student's test is confirmed using this method.

One of the most famous statistical tools is the Student's t test. It is used to measure the statistical significance of various pairwise quantities. Microsoft Excel has a special function for calculating this indicator. Let's learn how to calculate the Student's t-test in Excel.

But first, let’s find out what the Student’s t-test is in general. This indicator is used to check the equality of the average values ​​of two samples. That is, it determines the significance of the differences between two groups of data. At the same time, a whole set of methods is used to determine this criterion. The indicator can be calculated taking into account one-sided or two-sided distribution.

Calculation of an indicator in Excel

Now let's move directly to the question of how to calculate this indicator in Excel. It can be done through the function STUDENT TEST. In 2007 and earlier versions of Excel, it was called TTEST. However, it was left in later versions for compatibility purposes, but in them it is still recommended to use a more modern one - STUDENT TEST. This function can be used in three ways, which will be discussed in detail below.

Method 1: Function Wizard

The easiest way to calculate this indicator is through the Function Wizard.

The calculation is performed, and the result is displayed on the screen in a pre-selected cell.

Method 2: Working with the Formulas tab

Function STUDENT TEST can also be called by going to the tab "Formulas" using a special button on the ribbon.

Method 3: Manual Entry

Formula STUDENT TEST can also be entered manually into any cell on the worksheet or into the function row. Its syntactic form looks like this:

STUDENT TEST(Array1,Array2,Tails,Type)

What each of the arguments means was considered when analyzing the first method. These values ​​should be substituted into this function.

After the data has been entered, press the button Enter to display the result on the screen.

As you can see, calculating the Student's test in Excel is very simple and quick. The main thing is that the user who performs the calculations must understand what he is and what input data is responsible for what. The program performs the direct calculation itself.

The method allows you to test the hypothesis that the average values ​​of two general populations from which the compared ones are extracted dependent samples differ from each other. The assumption of dependence most often means that the trait is measured on the same sample twice, for example, before the intervention and after it. In the general case, each representative of one sample is assigned a representative from another sample (they are combined in pairs) so that the two data series are positively correlated with each other. Weaker types of sample dependence: sample 1 - husbands, sample 2 - their wives; sample 1 - one-year-old children, sample 2 is made up of twins of children in sample 1, etc.

Testable statistical hypothesis, as in the previous case, H 0: M 1 = M 2(the average values ​​in samples 1 and 2 are equal). If it is rejected, the alternative hypothesis is accepted that M 1 more (less) M 2.

Initial assumptions for statistical testing:

□ each representative of one sample (from one general population) is associated with a representative of another sample (from another general population);

□ data from two samples are positively correlated (form pairs);

□ the distribution of the studied characteristic in both samples corresponds to the normal law.

Source data structure: there are two values ​​of the studied feature for each object (for each pair).

Restrictions: the distribution of the characteristic in both samples should not differ significantly from normal; the data of the two measurements corresponding to both samples are positively correlated.

Alternatives: Wilcoxon T-test, if the distribution for at least one sample differs significantly from normal; t-Student test for independent samples - if the data for the two samples are not positively correlated.

Formula for the empirical value of the Student's t test reflects the fact that the unit of analysis for differences is difference (shift) characteristic values ​​for each pair of observations. Accordingly, for each of the N pairs of attribute values, the difference is first calculated d i = x 1 i - x 2 i.

(3) where M d – average difference of values; σ d – standard deviation of differences.

Calculation example:

Suppose, during testing the effectiveness of the training, each of the 8 group members was asked the question “How often do your opinions coincide with the opinions of the group?” - twice, before and after the training. A 10-point scale was used for responses: 1 - never, 5 - half the time, 10 - always. The hypothesis was tested that as a result of the training, the self-esteem of conformity (the desire to be like others in the group) of the participants would increase (α = 0.05). Let's create a table for intermediate calculations (Table 3).

Table 3

The arithmetic mean for the difference M d = (-6)/8= -0.75. Subtract this value from each d (the penultimate column of the table).

The formula for the standard deviation differs only in that d appears in it instead of X. We substitute all the necessary values, and we get

σ d = = 0.886.

Step 1. Calculate the empirical value of the criterion using formula (3): average difference Md= -0.75; standard deviation σ d = 0,886; t e = 2,39; df = 7.

Step 2. Using the table of critical values ​​of the t-Student criterion, we determine the p-level of significance. For df = 7, the empirical value is between the critical values ​​for p = 0.05 and p - 0.01. Therefore, p< 0,05.

Step 3. We make a statistical decision and formulate a conclusion. The statistical hypothesis of equality of means is rejected. Conclusion: the indicator of self-assessment of conformity of participants after the training increased statistically significantly (at significance level p< 0,05).

Parametric methods include comparison of variances of two samples according to the criterion F-Fisher. Sometimes this method leads to valuable meaningful conclusions, and in the case of comparing means for independent samples, comparing variances is mandatory procedure.

To calculate F em you need to find the ratio of the variances of the two samples, and so that the larger variance is in the numerator, and the smaller one is in the denominator.

Comparison of Variances. The method allows you to test the hypothesis that the variances of the two populations from which the compared samples are drawn differ from each other. Tested statistical hypothesis H 0: σ 1 2 = σ 2 2 (the variance in sample 1 is equal to the variance in sample 2). If it is rejected, the alternative hypothesis is accepted that one variance is greater than the other.

Initial assumptions: two samples are drawn randomly from different populations with a normal distribution of the characteristic being studied.

Source data structure: the characteristic being studied is measured in objects (subjects), each of which belongs to one of the two samples being compared.

Restrictions: the distributions of the trait in both samples do not differ significantly from normal.

Alternative method: Levene's test, the use of which does not require checking the assumption of normality (used in the SPSS program).

Formula for the empirical value of the Fisher's F test:

(4)

where σ 1 2 - large dispersion, and σ 2 2 - smaller dispersion. Since it is not known in advance which dispersion is greater, then to determine the p-level it is used Table of critical values ​​for non-directional alternatives. If F e > F Kp for the corresponding number of degrees of freedom, then r < 0,05 и статистическую гипотезу о равенстве дисперсий можно отклонить (для α = 0,05).

Calculation example:

The children were given regular arithmetic problems, after which one randomly selected half of the students were told that they had failed the test, and the rest were told the opposite. Each child was then asked how many seconds it would take them to solve a similar problem. The experimenter calculated the difference between the time the child called and the result of the completed task (in seconds). It was expected that the message of failure would cause some inadequacy in the child's self-esteem. The hypothesis being tested (at the α = 0.005 level) was that the variance of the aggregate self-esteem does not depend on reports of success or failure (H 0: σ 1 2 = σ 2 2).

The following data was obtained:

Step 1. Calculate the empirical value of the criterion and the number of degrees of freedom using formulas (4):

Step 2. According to the table of critical values ​​of the Fisher f-criterion for undirected alternatives we find the critical value for df number = 11; df know= 11. However, there is a critical value only for df number= 10 and df know = 12. It is impossible to take a larger number of degrees of freedom, so we take the critical value for df number= 10: For r = 0,05 F Kp = 3.526; For r = 0,01 F Kp = 5,418.

Step 3. Making a statistical decision and meaningful conclusion. Since the empirical value exceeds the critical value for r= 0.01 (and even more so for p = 0.05), then in this case p< 0,01 и принимается альтернативная гипо­теза: дисперсия в группе 1 превышает дисперсию в группе 2 (p< 0.01). Consequently, after a message about failure, the inadequacy of self-esteem is higher than after a message about success.

/ practical statistics / reference materials / student t-test values

Meaningt -Student's t-test at significance levels of 0.10, 0.05 and 0.01

ν – degrees of freedom of variation

Standard Student's t-test values

Table XI

Standard Fisher test values ​​used to assess the significance of differences between two samples

Student's t-test

Student's t-test- a general name for a class of methods for statistical testing of hypotheses (statistical tests) based on the Student distribution. The most common uses of the t-test involve testing the equality of means in two samples.

t-statistics is usually constructed according to the following general principle: the numerator is a random variable with zero mathematical expectation (if the null hypothesis is satisfied), and the denominator is the sample standard deviation of this random variable, obtained as the square root of the unmixed variance estimate.

Story

This criterion was developed by William Gosset to evaluate the quality of beer at Guinness. In connection with obligations to the company regarding non-disclosure of trade secrets (Guinness management considered the use of statistical apparatus in their work as such), Gosset’s article was published in 1908 in the journal Biometrics under the pseudonym “Student”.

Data requirements

To apply this criterion, it is necessary that the original data have a normal distribution. In the case of applying a two-sample test for independent samples, it is also necessary to comply with the condition of equality of variances. There are, however, alternatives to the Student's t test for situations with unequal variances.

The requirement of normal distribution of data is necessary for an accurate t (\displaystyle t) -test. However, even with other data distributions, it is possible to use t (\displaystyle t) -statistics. In many cases, this statistic asymptotically has a standard normal distribution - N (0, 1) (\displaystyle N(0,1)) , so quantiles of this distribution can be used. However, even in this case, often quantiles are used not of the standard normal distribution, but of the corresponding Student distribution, as in the exact t (\displaystyle t) test. They are asymptotically equivalent, but in small samples the confidence intervals of the Student distribution are wider and more reliable.

One-sample t-test

Used to test the null hypothesis H 0: E (X) = m (\displaystyle H_(0):E(X)=m) about the equality of the mathematical expectation E (X) (\displaystyle E(X)) to some known value m ( \displaystyle m) .

Obviously, if the null hypothesis is satisfied, E (X ¯) = m (\displaystyle E((\overline (X)))=m) . Taking into account the assumed independence of observations, V (X ¯) = σ 2 / n (\displaystyle V((\overline (X)))=\sigma ^(2)/n) . Using an unbiased variance estimate s X 2 = ∑ t = 1 n (X t − X ¯) 2 / (n − 1) (\displaystyle s_(X)^(2)=\sum _(t=1)^(n )(X_(t)-(\overline (X)))^(2)/(n-1)) we obtain the following t-statistics:

t = X ¯ − m s X / n (\displaystyle t=(\frac ((\overline (X))-m)(s_(X)/(\sqrt (n)))))

Under the null hypothesis, the distribution of this statistic is t (n − 1) (\displaystyle t(n-1)) . Consequently, if the absolute value of the statistics exceeds the critical value of a given distribution (at a given significance level), the null hypothesis is rejected.

Two-sample t-test for independent samples

Let there be two independent samples of volumes n 1, n 2 (\displaystyle n_(1)~,~n_(2)) of normally distributed random variables X 1, X 2 (\displaystyle X_(1),~X_(2)). It is necessary to test the null hypothesis of equality of mathematical expectations of these random variables H 0: M 1 = M 2 (\displaystyle H_(0):~M_(1)=M_(2)) using sample data.

Consider the difference between sample means Δ = X ¯ 1 − X ¯ 2 (\displaystyle \Delta =(\overline (X))_(1)-(\overline (X))_(2)) . Obviously, if the null hypothesis is true E (Δ) = M 1 − M 2 = 0 (\displaystyle E(\Delta)=M_(1)-M_(2)=0) . The variance of this difference is equal, based on the independence of the samples: V (Δ) = σ 1 2 n 1 + σ 2 2 n 2 (\displaystyle V(\Delta)=(\frac (\sigma _(1)^(2))( n_(1)))+(\frac (\sigma _(2)^(2))(n_(2)))) . Then using the unbiased variance estimate s 2 = ∑ t = 1 n (X t − X ¯) 2 n − 1 (\displaystyle s^(2)=(\frac (\sum _(t=1)^(n)( X_(t)-(\overline (X)))^(2))(n-1))) we obtain an unbiased estimate of the variance of the difference between sample means: s Δ 2 = s 1 2 n 1 + s 2 2 n 2 (\ displaystyle s_(\Delta )^(2)=(\frac (s_(1)^(2))(n_(1)))+(\frac (s_(2)^(2))(n_(2) ))) . Therefore, the t-statistic for testing the null hypothesis is

T = X ¯ 1 − X ¯ 2 s 1 2 n 1 + s 2 2 n 2 (\displaystyle t=(\frac ((\overline (X))_(1)-(\overline (X))_( 2))(\sqrt ((\frac (s_(1)^(2))(n_(1)))+(\frac (s_(2)^(2))(n_(2))))) ))

If the null hypothesis is true, this statistic has a distribution t (d f) (\displaystyle t(df)), where d f = (s 1 2 / n 1 + s 2 2 / n 2) 2 (s 1 2 / n 1) 2 / (n 1 − 1) + (s 2 2 / n 2) 2 / (n 2 − 1) (\displaystyle df=(\frac ((s_(1)^(2)/n_(1)+s_(2 )^(2)/n_(2))^(2))((s_(1)^(2)/n_(1))^(2)/(n_(1)-1)+(s_(2 )^(2)/n_(2))^(2)/(n_(2)-1))))

Case of equal variance

If the variances of the samples are assumed to be equal, then

V (Δ) = σ 2 (1 n 1 + 1 n 2) (\displaystyle V(\Delta)=\sigma ^(2)\left((\frac (1)(n_(1)))+(\ frac (1)(n_(2)))\right))

Then the t-statistic is:

T = X ¯ 1 − X ¯ 2 s X 1 n 1 + 1 n 2 , s X = (n 1 − 1) s 1 2 + (n 2 − 1) s 2 2 n 1 + n 2 − 2 (\ displaystyle t=(\frac ((\overline (X))_(1)-(\overline (X))_(2))(s_(X)(\sqrt ((\frac (1)(n_(1 )))+(\frac (1)(n_(2))))))~,~~s_(X)=(\sqrt (\frac ((n_(1)-1)s_(1)^ (2)+(n_(2)-1)s_(2)^(2))(n_(1)+n_(2)-2))))

This statistic has distribution t (n 1 + n 2 − 2) (\displaystyle t(n_(1)+n_(2)-2))

Two-sample t-test for dependent samples

To calculate the empirical value of the t (\displaystyle t) -criterion in the situation of testing a hypothesis about differences between two dependent samples (for example, two samples of the same test with a time interval), the following formula is used:

T = M d s d / n (\displaystyle t=(\frac (M_(d))(s_(d)/(\sqrt (n)))))

where M d (\displaystyle M_(d)) is the average difference of values, s d (\displaystyle s_(d)) is the standard deviation of the differences, and n is the number of observations

This statistic has a distribution t (n − 1) (\displaystyle t(n-1)) .

Testing a Linear Constraint on Linear Regression Parameters

The t-test can also test an arbitrary (single) linear constraint on the parameters of a linear regression estimated by ordinary least squares. Let it be necessary to test the hypothesis H 0: c T b = a (\displaystyle H_(0):c^(T)b=a) . Obviously, if the null hypothesis is satisfied, E (c T b ^ − a) = c T E (b ^) − a = 0 (\displaystyle E(c^(T)(\hat (b))-a)=c^( T)E((\hat (b)))-a=0) . Here we use the property of unbiased least squares estimates of the model parameters E (b ^) = b (\displaystyle E((\hat (b)))=b) . In addition, V (c T b ^ − a) = c T V (b ^) c = σ 2 c T (X T X) − 1 c (\displaystyle V(c^(T)(\hat (b))-a )=c^(T)V((\hat (b)))c=\sigma ^(2)c^(T)(X^(T)X)^(-1)c) . Using instead of the unknown variance its unbiased estimate s 2 = E S S / (n − k) (\displaystyle s^(2)=ESS/(n-k)) we obtain the following t-statistics:

T = c T b ^ − a s c T (X T X) − 1 c (\displaystyle t=(\frac (c^(T)(\hat (b))-a)(s(\sqrt (c^(T) (X^(T)X)^(-1)c)))))

This statistic, when the null hypothesis is satisfied, has a distribution t (n − k) (\displaystyle t(n-k)) , so if the value of the statistic is higher than the critical value, then the null hypothesis of a linear constraint is rejected.

Testing hypotheses about the linear regression coefficient

A special case of a linear constraint is testing the hypothesis that the regression coefficient b j (\displaystyle b_(j)) is equal to a certain value a (\displaystyle a) . In this case, the corresponding t-statistic is:

T = b ^ j − a s b ^ j (\displaystyle t=(\frac ((\hat (b))_(j)-a)(s_((\hat (b))_(j)))))

where s b ^ j (\displaystyle s_((\hat (b))_(j))) is the standard error of the coefficient estimate - the square root of the corresponding diagonal element of the covariance matrix of the coefficient estimates.

If the null hypothesis is true, the distribution of this statistic is t (n − k) (\displaystyle t(n-k)) . If the absolute value of the statistic is higher than the critical value, then the difference between the coefficient and a (\displaystyle a) is statistically significant (non-random), otherwise it is insignificant (random, that is, the true coefficient is probably equal to or very close to the estimated value of a (\ display style a))

Comment

A one-sample test for mathematical expectations can be reduced to testing a linear constraint on the parameters of a linear regression. In a one-sample test, this is a "regression" on a constant. Therefore, s 2 (\displaystyle s^(2)) of regression is a sample estimate of the variance of the random variable being studied, the matrix X T X (\displaystyle X^(T)X) is equal to n (\displaystyle n) , and the estimate of the “coefficient” of the model is equal to sample mean. From here we obtain the expression for the t-statistic given above for the general case.

Similarly, it can be shown that a two-sample test with equal sample variances also reduces to testing linear constraints. In a two-sample test, this is a "regression" on a constant and a dummy variable identifying the subsample depending on the value (0 or 1): y = a + b D (\displaystyle y=a+bD) . The hypothesis about the equality of the mathematical expectations of the samples can be formulated as a hypothesis about the equality of the coefficient b of this model to zero. It can be shown that the appropriate t-statistic for testing this hypothesis is equal to the t-statistic given for the two-sample test.

It can also be reduced to checking the linear constraint in the case of different dispersions. In this case, the model error variance takes two values. From this you can also obtain a t-statistic similar to that given for the two-sample test.

Nonparametric analogues

An analogue of the two-sample test for independent samples is the Mann-Whitney U test. For the situation with dependent samples, the analogues are the sign test and the Wilcoxon T-test

Literature

Student. The probable error of a mean. // Biometrika. 1908. No. 6 (1). P. 1-25.

Links

On the criteria for testing hypotheses about the homogeneity of means on the website of the Novosibirsk State Technical University