Carrying out any statistical analysis is unthinkable without calculations. In this article, we will look at how to calculate the variance, standard deviation, coefficient of variation and other statistical indicators in Excel.

Maximum and minimum value

Average linear deviation

The average linear deviation is the average of the absolute (modulo) deviations from in the analyzed data set. The mathematical formula looks like:

a is the average linear deviation,

X- analyzed indicator,

X̅- the average value of the indicator,

n

In Excel this function is called SROTCL.

After selecting the SIRT function, we specify the data range for which the calculation should take place. Click "OK".

Dispersion

(module 111)

Perhaps not everyone knows what is, so I will explain - this is a measure that characterizes the spread of data around the mathematical expectation. However, there is usually only a sample available, so the following variance formula is used:

s2 is the sample variance calculated from observational data,

X– individual values,

X̅ is the arithmetic mean over the sample,

n is the number of values ​​in the analyzed data set.

The corresponding Excel function is − DISP.G. When analyzing relatively small samples (up to about 30 observations), you should use , which is calculated by the following formula.

The difference, apparently, is only in the denominator. Excel has a function to calculate the sample unbiased variance DISP.B.

Select the desired option (general or selective), specify the range, click the "OK" button. The resulting value may be very large due to the preliminary squaring of the deviations. Dispersion in statistics is a very important indicator, but it is usually used not in its pure form, but for further calculations.

Standard deviation

Standard deviation (RMS) is the root of the variance. This indicator is also called the standard deviation and is calculated by the formula:

by general population

by sample

You can simply take the root of the variance, but there are ready-made functions for standard deviation in Excel: STDEV.G And STDEV.B(for the general and sample population, respectively).

Standard and standard deviation, I repeat, are synonyms.

Next, as usual, specify the desired range and click on "OK". The standard deviation has the same units of measurement as the analyzed indicator, therefore it is comparable with the original data. More on that below.

The coefficient of variation

All the indicators discussed above are linked to the scale of the initial data and do not allow one to get a figurative idea of ​​the variation of the analyzed population. To obtain a relative measure of data scatter, use the coefficient of variation, which is calculated by dividing standard deviation on average. The formula for the coefficient of variation is simple:

To calculate the coefficient of variation in Excel, there is no ready-made function, which is not a big problem. The calculation can be made by simply dividing the standard deviation by the mean. To do this, in the formula bar, write:

STDEV.G()/AVERAGE()

The data range is indicated in parentheses. If necessary, use the standard deviation for the sample (STDEV.B).

The coefficient of variation is usually expressed as a percentage, so a cell with a formula can be framed with a percentage format. The desired button is located on the ribbon on the "Home" tab:

You can also change the format by selecting from the context menu after selecting the desired cell and clicking the right mouse button.

The coefficient of variation, unlike other indicators of the spread of values, is used as an independent and very informative indicator of data variation. In statistics, it is generally accepted that if the coefficient of variation is less than 33%, then the data set is homogeneous, if more than 33%, then it is heterogeneous. This information can be useful for a preliminary description of the data and for identifying opportunities for further analysis. In addition, the coefficient of variation, measured as a percentage, makes it possible to compare the degree of dispersion of different data, regardless of their scale and units of measurement. Useful property.

Oscillation factor

Another measure of data scatter today is the oscillation coefficient. This is the ratio of the range of variation (the difference between the maximum and minimum values) to the mean. There is no ready-made Excel formula, so you have to put together three functions: MAX, MIN, AVERAGE.

The oscillation coefficient shows the degree of variation relative to the mean, which can also be used to compare different datasets.

In general, with the help of Excel, many statistical indicators are calculated very simply. If something is not clear, you can always use the search box in the function insert. Well, Google to the rescue.

And now I suggest you watch the video tutorial.

The Excel program is highly valued by both professionals and amateurs, because a user of any level of training can work with it. For example, anyone with minimal skills of "communication" with Excel can draw a simple graph, make a decent sign, etc.

At the same time, this program even allows you to perform various kinds of calculations, for example, calculation, but this already requires a slightly different level of training. However, if you have just started a close acquaintance with this program and are interested in everything that will help you become a more advanced user, this article is for you. Today I will tell you what the standard deviation formula in excel is, why it is needed at all and, in fact, when it is applied. Go!

What it is

Let's start with theory. The standard deviation is usually called the square root, obtained from the arithmetic mean of all squared differences between the available values, as well as their arithmetic mean. By the way, this value is usually called the Greek letter "sigma". The standard deviation is calculated using the formula STDEV, respectively, the program does it for the user itself.

The essence of this concept is to identify the degree of variability of the instrument, that is, it is, in its own way, an indicator from descriptive statistics. It reveals changes in the volatility of the instrument in any time period. Using STDEV formulas, you can estimate the standard deviation of a sample, while boolean and text values ​​are ignored.

Formula

Helps to calculate the standard deviation in excel formula, which is automatically provided in Excel. To find it, you need to find the formula section in Excel, and already there select the one that has the name STDEV, so it's very simple.

After that, a window will appear in front of you in which you will need to enter data for the calculation. In particular, two numbers should be entered in special fields, after which the program will automatically calculate the standard deviation for the sample.

Undoubtedly, mathematical formulas and calculations are a rather complicated issue, and not all users can deal with it right off the bat. However, if you dig a little deeper and understand the issue a little more in detail, it turns out that not everything is so sad. I hope you are convinced of this by the example of calculating the standard deviation.

Video to help

Andrey Lipov

In simple terms, the standard deviation shows how much the price of an instrument fluctuates over time. That is, the larger this indicator, the stronger the volatility or variability of a number of values.

The standard deviation can and should be used to analyze sets of values, since two sets with seemingly the same mean can turn out to be completely different in terms of the spread of values.

Example

Let's take two rows of numbers.

a) 1,2,3,4,5,6,7,8,9. Average - 5. Art. deviation = 2.7386

b) 20.1.7.1.15, -1, -20.4,18.5. Average - 5. Art. deviation = 12.2066

If you do not keep the entire series of numbers in front of your eyes, then the standard deviation shows that in the case of "b" the values ​​\u200b\u200bare scattered much more around their average value.

Roughly speaking, in row "b" the value is 5 plus or minus 12 (on average) - not exactly, but reveals the meaning.

How to Calculate Standard Deviation

To calculate the standard deviation, you can use the formula borrowed from the calculation of the standard deviation of mutual fund returns:

Here N is the number of values,
DOHaverage - the average of all values,
DOH period - the value of N.

In Excel, the corresponding function is called STDEV (or STDEV in the English version of the program).

The step by step instructions are:

1. Calculate the average for a series of numbers.
2. For each value, determine the difference between the mean and this value.
3. Calculate the sum of the squares of these differences.
4. Divide the resulting sum by the number of numbers in the series.
5. Take the square root of the number obtained in the last paragraph.

Your friends will benefit from this information. Share with them!

The coefficient of variation is a comparison of the dispersion of two randomly taken values. The values ​​have units, which results in a comparable result. This coefficient is needed to prepare the statistical analysis.

It allows investors to calculate risk indicators before making contributions to the selected assets. It is useful when the selected assets have different returns and risks. For example, one asset may have a high income and the degree of risk is also high, while another, on the contrary, has a low income and the degree of risk is correspondingly lower.

Standard deviation calculation

The standard deviation is a statistic. By calculating this value, the user will receive information about how much the data deviates in one direction or another relative to the average value. The standard deviation in Excel is calculated in several steps.

Prepare data: open the page where the calculations will take place. In our case, this is a picture, but it can be any other file. The main thing is to collect the information that you will use in the table for the calculation.

Enter data into any spreadsheet editor (in our case, Excel), filling in the cells from left to right. Should start from column "A". Headings are entered in the line at the top, and the names in the same columns that refer to the headings, only below. Then the date and the data to be calculated to the right of the date.

Save this document.

Now let's move on to the calculation itself. Highlight a cell with the cursor after the last entered value from below.

Enter the sign "=" and then write the formula. The equal sign is required. Otherwise, the program will not consider the proposed data. The formula is entered without spaces.

The utility will display the names of several formulas. Choose " STDEV". This is the formula for calculating the standard deviation. There are two types of calculation:

• with calculation by sample;
• with the calculation of the general population.

By selecting one of them, specify the data range. The entire formula entered will look like this: "= STDEV (B2: B5)".

Then click on the button " Enter". The received data will appear in the marked item.

Calculation of the arithmetic mean

Calculated when the user needs to generate a report, for example, on payroll in his company. This is done as follows:

• only select range and click on the "Enter" button. And the cell will now display the result from the data taken above.

Calculation of the coefficient of variation

The formula for calculating the coefficient of variation:

V= S/X where S is the standard deviation and X is the mean.

In order to calculate the coefficient of variation in Excel, you need to find the standard deviation and the arithmetic mean. That is, after doing the first two calculations that were shown above, you can proceed to work on the coefficient of variation.

To do this, open Excel, fill in two fields, where you should enter the received numbers of standard deviation and average value.

Now select the cell that was assigned to the number to calculate the variation. Open the tab " home' if it is not open. Click on the tool Number". Choose percentage format.

Go to the marked cell and double click on it. Then enter an equal sign and highlight the item where the standard deviation total is entered. Then click on the keyboard on the "slash" or "split" button (it looks like this: "/"). Highlight an item, where the arithmetic mean is entered, and click on the "Enter" button. It should turn out like this:

And here is the result after pressing "Enter":

Also, to calculate the coefficient of variation, you can use online calculators, such as planetcalc.ru and allcalc.ru. It is enough to enter the necessary numbers and start the calculation, and then get the necessary information.

standard deviation

The standard deviation in Excel is solved using two formulas:

In simple terms, the root of the variance is taken. How to calculate the variance is discussed below.

The standard deviation is synonymous with the standard deviation and the exact one is calculated as well. The cell for the result under the numbers to be calculated is highlighted. One of the functions shown in the figure above is inserted. The button " Enter". The result is received.

Oscillation coefficient

The ratio of the range of variation to the mean is called the oscillation coefficient. There are no ready-made formulas in Excel, so need to compose several functions in one.

The functions to be assembled are the mean, maximum, and minimum formulas. This factor is used to compare the data set.

Dispersion

Dispersion is a function that characterize the spread of data around mathematical expectation. Calculated according to the following equation:

Variables take the following values:

There are two functions in Excel that determine the variance:

To make a calculation, a cell is highlighted under the numbers to be calculated. Go to the insert function tab. Choose a category " Statistical". In the drop-down list, select one of the functions and click on the "Enter" button.

Maximum and minimum

The maximum and minimum are needed in order not to manually search for the minimum or maximum number among a large number of numbers.

To calculate the maximum select the entire range necessary numbers in the table and a separate cell, then click on the icon "Σ" or " AutoSum". In the drop-down window, select "Maximum" and by pressing the "Enter" button you get the desired value.

Do the same to get the minimum. Just select the "Minimum" function.

DEFINITION OF THE GENERAL COLLECT AND

PARAMETERS BASED ON SAMPLE STATISTICS;

AVERAGE AND STANDARD DEVIATION

Determination of the population mean

(general population)

In the reaction time experiment described in the Appendix to Chapter 1, the results of an actual experiment were taken. It was assumed that they represent data that could be obtained in an experiment with full internal validity. Thus, the average response time to a light signal over 17 samples was the average that could be obtained in an experiment with an unlimited number of samples.

We use a limited sample mean to infer a sufficiently large (up to unlimited) sample population. Such a population is called the general population. The average over the general population of such, for example, data as BP is denoted by M x. Such a characteristic of the general population is called a parameter. The average actually calculated by us for a given sample is called a statistic, and is denoted by M x. Is the M x statistic the best estimate of M x that we can get from our sample? The answer is - without proof - yes. But before you decide that this is always the case, let's move on to standard deviation, where this is not the case.

Calculating the Standard Deviation

Usually, in addition to the average of the scores, we want to know something else, namely, what is the non-systematic variation of the scores from sample to sample. The most common way to measure non-systematic variation is to calculate the standard deviation.

To do this, you determine how much each score (i.e. X) more or less than the average ( M X). You then square each difference ( X-M X) and add them up. Then you divide this sum by N number of samples. Finally, you take the square root of this average.

This calculation is represented by a formula using the symbol σ x to denote the standard deviation:

90This formula can be shortened by introducing a small x to represent ( X-M X). Then the formula looks like this:

(2.1A)

Let's write out the data on condition A from the appendix to chapter I and at the same time make calculations on them, indicated by the formula for σ x

Because the

ms.

91Standard Deviation Estimation

population

In order to determine the population mean, which could be obtained in an infinite experiment, the best estimate was actually the sample mean. The situation is different with the standard deviation. In any set of real samples, there are fewer very high or very low results than in the general population. And since the standard deviation is a measure of the spread of estimates, its value, determined on the basis of the sample, is always less than the population parameter sigma σ x.

More accurate, the estimate of the standard deviation for the population is found by the formula

(2.2)

(2.2A)

For our numerical data:

ms.

Some experiments hypothesize that behavior in one condition is more variable than in another. It is then more appropriate to compare standard deviations rather than averages. If for both conditions N the same thing, you can compare sigma with each other. However, when N are different, sigma for the condition with less N gives a lower estimate of such a population parameter as the standard deviation. Therefore, two should be compared S.

The table below will help you remember these points and formulas.92

Task: Calculate σ x and S x for condition B.

Answer:σ B = 15.9; σ B = 16.4.