OmniDocExcel standard deviation calculation formula: usage of STDEV function
To calculate standard deviation in Excel, the key is to choose the right function: use STDEV.S for the sample and STDEV.P for the population. This article explains the difference between the two, the input steps, the results verification method, and what to do if you don’t have Excel.
Key points of this article
- Use STDEV.P when the data is all objects of the population, and use STDEV.S when it is just a sample obtained by sampling. The denominators of the two are n and n-1 respectively.
- The calculation results of STDEV and STDEV.S in the old version of Excel are the same. In the new form, it is recommended to write STDEV.S directly to make the meaning clearer.
- When entering a formula, the data area should only contain numerical values. Text and empty cells will be ignored by the function, but it is necessary to prevent incorrect data from being mixed in.
- Use SQRT plus VAR.S to calculate it manually and compare it with the function results to quickly find the problem of wrong area selection.
- When Excel is not installed, you can use an online spreadsheet or calculator, but you still need to use the same set of data to check the results.
Many people need to calculate the standard deviation of a set of data when doing experimental reports, quality statistics, or coursework. Hand calculation requires first finding the average, then squaring, summing, dividing by the number, and taking the square root one by one. It is easy to make mistakes when there are too many data. Excel comes with statistical functions, and you can get results in a few seconds, provided you choose the right function.
This article is written for people who need to quickly get the standard deviation in Excel, as well as readers who just want to find a "standard deviation calculation formula excel online tool". After reading, you will know the difference between STDEV.S, STDEV.P and the old version of STDEV, be able to enter the formula accordingly, and use a simple method to check whether the result is reliable.
To calculate the standard deviation, which function is used in Excel? One sentence conclusion
To calculate standard deviation in Excel, use STDEV.S when the data represents a sample, and use STDEV.P when the data is all objects; STDEV in the old version of the table is equivalent to STDEV.S. Just enter the formula directly after selecting the data range.
| your situation | Recommended practices | reason |
|---|---|---|
| The data is a part extracted from the whole, and we want to estimate the overall fluctuation | =STDEV.S(data area) | Using n-1 as the denominator provides a more reasonable estimate of the population standard deviation. |
| The data is all the research object, such as the scores of the whole class of 30 people | =STDEV.P(data area) | The denominator is n, which directly describes the degree of discreteness of this set of data itself. |
| Using an earlier version of Excel, or someone else's spreadsheet already has STDEV | =STDEV(data area) | The calculation results of STDEV and STDEV.S are the same, just keep them. |
| Want to check whether the function result is correct | =SQRT(VAR.S(data area)) | Calculate the variance first and then take the square root, which should be consistent with STDEV.S |
| There are text, logical values or spaces in the data area | Select only the area where the value is located, or clean the data first | The function will ignore text and empty cells in the reference, but selecting the wrong range will directly change the result. |
Before you start: Confirm these points first
First determine whether the data is a sample or a population. This is the basis for choosing STDEV.S or STDEV.P. For example, if 50 people are randomly selected from 1,000 students to measure their height, these 50 people are samples; the school only has 30 class scores to analyze, then these 30 numbers are the population. If you choose the wrong function, the results will be slightly biased, and the bias will be more obvious when there is less data.
Confirm that the data is all in the same column or row. Standard deviation is for a set of numbers. If there are headers, units, or total rows mixed in a column, the total value will be treated as data and participated in the calculation, and the result will be distorted. It is recommended to exclude the title row from the area first.
Check if the number is really a number. Data copied from web pages, PDFs or other software sometimes appears to be numbers but is actually in text format. The text will not be involved in the calculation, and the result will be too small or an error will be reported. You can use the COUNT function to count the number of values in a range and compare it with your expected number.
Note the number of data. n is used when checking the results, which is the number of data. You can get it with COUNT(data range).
Determine which Excel version to use. Versions after 2010 support STDEV.S and STDEV.P. Earlier versions did not have these two functions and could only use STDEV and STDEVP. If you want to send your form to others, it is recommended to ask them which version they use.
Steps to calculate standard deviation in Excel
The following is an example of eight numbers: 2, 4, 4, 4, 5, 5, 7, 9, fill in cells A2 to A9 in turn.
- Fill in the data in the table one by one into the same column, such as A2 to A9, do not leave blank lines and do not write units next to numbers.
two. Click the blank cell, such as C2, in which you want to display the results.
- Enter the sample standard deviation formula = STDEV.S (A2:A9), press enter, the result is about 2.138.
- If the data is overall, enter = STDEV.P (A2:A9) in another cell, press enter, and the result is 2.
- When you need to check, enter = SQRT (VAR.S (A2:A9)) in the blank cell, and the result should be the same as step 3.
- If the data range is long, you can drag the mouse to the end of the area, or directly rewrite the cell address, such as A2:A500, to avoid missing the data.
- The result is # DIV/0! Indicates that the number of values in the area is less than the required number: STDEV.S requires at least two values, and only one number in the area cannot be calculated.
- Keep the result as the desired number of decimal places and mark "sample" or "population" next to it for easy reading later.
# check item by item after completion
After you get the results, don't write them directly into the report. Check the following points item by item to avoid most mistakes.
| Check item | What do you think? | What if there's a problem? |
|---|---|---|
| Whether the area is complete | Check whether the address in the formula covers all the data, such as whether A2:A9 contains the last number. | Modify the area address or use COUNT to compare the number of data |
| Whether to mix the title or total | Check whether the cell at the beginning and end of the region is a number. | Recalculate after moving the title and total lines out of the area |
| Whether the choice of function is correct | Judge whether it is a sample or a population against the source of the data | Change to STDEV.S for the sample, STDEV.P for the whole, and update the annotation. |
| Is it consistent with the manual method | Compare the results of STDEV.S and SQRT (VAR.S) | Check areas, text formatting, and hidden characters in case of inconsistency |
| Is the quantity abnormal? | Use COUNT to confirm the number of values involved in the calculation | When the number is too small, check to see if there are text numbers or empty cells |
If the data comes from a table in the scanned version of PDF, you can first use the PDF editor in the desktop client to do OCR, then export it to Excel, and then check it according to the above method. OCR recognition results also need to be checked one by one, especially paying attention to decimal points, negative signs and similar numbers, such as 3 and 8, 1 and 7.
# Core comparison
| Function | Denominator | Applicable data | Sample results (8 above) |
|---|---|---|---|
| STDEV.S | n-1 | Sample | About 2.138 |
| STDEV.P | n | Overall | 2 |
| STDEV (old version) | n-1 | Sample, same as STDEV.S | About 2.138 |
| STDEVP (old version) | n | Overall, same as STDEV.P | 2 |
The sum of square deviations of this set of numbers is 32, with an average of 5. The total variance is 32 divided by 8 to get 4, and the square is 2; the sample variance is 32 divided by 7, which is about 4.571, and the square is about 2.138. There is little difference between the two results, but when there is less data, choosing the wrong function will make a significant difference.
# frequently asked questions (FAQ)
1. Without Excel installed, can you use online tools to calculate the standard deviation?
Sure. A common practice is to use an online form or an online statistical calculator to paste the data and select the sample or overall options. Test once with the above 8 before use, the result should be 2.138 or 2, change to another tool if the result does not match, or use manual formula to calculate.
2. What is the relationship between standard deviation and variance?
Variance is the average of the square of the difference between each number and the average, and the standard deviation is the square root of the variance. The corresponding functions in Excel are VAR.S and VAR.P. The unit of standard deviation is the same as the original data, so it is more intuitive to read.
3. If there are empty cells in the data, will it affect the result?
STDEV.S and STDEV.P ignore the empty cells in the range, so they calculate the rest of the values. At this time, the number of data decreases, and the results will change accordingly. If the blank space represents "0" instead of "no data", you need to fill in the blank space as 0 first.
4. Why is the result of Excel different from what I calculated by hand?
The common reason is that the denominator is mischosen: when calculating by hand, you should use n or nmur1, which should be consistent with the function. Secondly, the data area is incomplete or mixed with text. You can first compare the results of SQRT (VAR.S), and then check the data item by item.
5. After the standard deviation is calculated, how to explain it?
The larger the standard deviation is, the more dispersed the data is; the smaller the standard deviation is, the more concentrated the data is near the average. When explaining, it is suggested to look at it together with averages. For example, "average score 75 points, standard deviation 2.1 points" means that most grades are close to 75 points.
# Summary
The key to calculating the standard deviation formula in Excel is to judge whether the data belongs to the sample or the population, and then choose STDEV.S or STDEV.P. After entering the area, check it manually with SQRT and VAR.S, and the result is more reliable. Results involving OCR or online tools are also recommended for sample review to confirm that the figures are correct before using them.
