OmniDocHow to calculate statistical formulas in Excel: variance, range and average
No need to find an online calculator, use functions to write out the average, range, variance, proportion and difference directly in the Excel table. The writing method of each formula, cell reference techniques, and how to check the results after completion are given.
Key points of this article
- Use AVERAGE for the average, MAX minus MIN for the range, and VAR.S or VAR.P for the variance. Just write it directly in the cell.
- Select VAR.S for sample data and VAR.P for overall data. Wrong selection will make the variance result larger or smaller. Please confirm the nature of the data before taking action.
- The proportion and difference are written once using relative references and fixed references, and then filled downwards to avoid manual calculation or repeated pasting.
- After finishing, use COUNT, hand calculation of small samples or calculator to check, which is more reliable than just looking at the result numbers.
When many people encounter statistical needs, they subconsciously search for "online tools for finding formulas in excel", paste a column of numbers into a web page, calculate it, and then copy it back to the table. This can be done when there is little data. However, when there is too much data and it needs to be modified repeatedly, it is prone to errors and the retention process is inconvenient.
In fact, Excel itself has corresponding functions or simple operations for average value, range, variance, proportion and difference. They are written in the table, and the results are automatically updated when the data is changed. This article gives how to write each statistic, explains how to determine which variance function to use, and introduces how to check the results after completion.
How to write statistical formulas directly in the table? One sentence conclusion
Enter the equal sign plus function directly into the result cell and use the cell reference to point to the original data without relying on an external calculator. Choose the writing method below according to your situation.
| your situation | Recommended practices | reason |
|---|---|---|
| Want the average of a data column | Use the AVERAGE function to reference an entire range of columns | Results are automatically updated after data changes |
| The difference between the large value and the small value | Subtract MIN from MAX, which is the range | No sorting is required, the formula can be understood at a glance |
| Data is all objects (such as class scores) | Calculate population variance using VAR.P | The denominator is n, which meets the overall definition |
| The data is only a sampling (such as 30 questionnaires randomly checked) | Calculate sample variance with VAR.S | The denominator is n−1, which is more suitable for estimating the population |
| Want the proportion or gap between two columns | Divide, subtract, and fill down using cells | Each line is calculated independently for easy checking one by one. |
Before you start: Confirm these points first
Before writing the formula, there are several things that will directly affect whether the result is correct.
- Whether the data are all numbers. Numbers copied from web pages or documents often exist in text form. There may be a green triangle in the upper left corner. When selected, the status bar does not display the sum. Text numbers will not be counted by functions such as AVERAGE, and the result will be too small without reporting an error.
- Is the data a population or a sample. This determines whether VAR.P or VAR.S should be used for the variance. The score of the whole class of 40 people is the population; 30 questionnaires taken from the 1,000 questionnaires are the sample.
- The meaning of blank cells and zeros. Blank cells will be ignored by AVERAGE, but if they mean "no data" rather than "numeric value is zero", they should remain blank; if 0 is filled in by mistake, the average will be pulled down.
- Version differences. In earlier Excel versions, the variance function names are VAR and VARP, and the functions correspond to VAR.S and VAR.P. If you can't find the function name with a dot in your software, just replace it with the old name.
- Whether divide-by-zero protection is required. When doing proportions, an incorrect value will appear if the denominator is 0. You can wrap a layer of IFERROR around the formula, but first confirm the meaning of 0.
Steps to write out the average, range, variance, proportion and difference one by one in Excel
The following is a 12-row score table as an example. The scores are from B2 to B13, and there is another column from C2 to C13 which is the corresponding second test score.
- Organize the original data into consecutive columns. Each column has a header. Do not insert total rows or blank rows in the middle, otherwise the function will include them in the calculation.
- Enter the equal sign in a blank cell (for example, E2), then enter AVERAGE, select B2 to B13 in the brackets, and press Enter to get the average.
- Enter =MAX(B2:B13)-MIN(B2:B13) in E3 to get the range. This way of writing does not require sorting, the large and small values will be found automatically.
- Enter =VAR.S(B2:B13) in E4 to get the sample variance. If these 12 people are all the objects, use =VAR.P(B2:B13) instead. The two results should be labeled separately to avoid confusion.
- Proportion calculation: Enter =C2/B2 in D2, which represents the ratio of the second score to the first score. If you want to account for the proportion of the total, write it as =B2/SUM(B2:B13), then select the area address in the brackets of SUM and press the F4 key to fix it. The denominator area will not shift when you drag it down.
- Difference calculation: Enter =C2-B2 in D2, which is the difference between the two scores, then select D2 and drag it down, and the entire column will be automatically filled.
- Format setting: After the proportion result is selected, it is set to percentage format. The average and variance retain appropriate decimal places to avoid a long string of decimals affecting reading.
- Mark the result area clearly and write a line of explanation next to it, such as "Variance is calculated based on samples", so that others or yourself in the future can understand the basis of the formula.
If you are using the UniCell table in the OmniDoc desktop client, you can first open the file, confirm whether it supports the above functions, and then organize the data according to the same idea.
Check each item after completion
When the formula is finished, the results come out, so don't use it directly. Most of the problems can be found in the following examination.
| Check item | What do you think? | What if there's a problem? |
|---|---|---|
| Number of data items | Count the number of numbers with COUNT (B2:B13) in the blank cell, which should be equal to 12. | The quantity is wrong. Check if there are any text numbers or missing lines. |
| Average value | Use the calculator to calculate the sum of several hands and divide the number by the number, and compare it with the AVERAGE result. | In case of inconsistency, check whether the text is mixed or white space is treated as zero. |
| Extreme difference | Find out the large and small values in the table, subtract, and compare with the results of the formula. | When the result is 0, check whether the area is selected correctly, or whether the data are all the same. |
| Variance type | Confirm whether the formula uses VAR.S or VAR.P, which is consistent with the nature of the data. | If the type is wrong, use the correct function and re-label it. |
| Proportion and difference | Randomly select two lines to calculate by hand to see if the reference moves with the line. | The results of the whole column are checked at the same time to see if the relative reference is missed or whether the fixed reference is misset. |
| Error value | Check to see if # DIV/0 appears! Or # VALUE! | The former checks whether the denominator is zero, while the latter checks whether there is text mixing. |
# Core comparison
Average, range and variance describe the overall characteristics of a group of data, while proportion and difference describe the relationship between the two groups of data. The following table shows the five statistics together to make it easier to compare the writing methods and error-prone areas.
| Statistic quantity | Common writing method | Data requirement | Common error points |
|---|---|---|---|
| Average value | AVERAGE (area) | Numbers in the selected area | The text number is ignored and the result is less. |
| Extreme difference | MAX (area)-MIN (region) | Numbers in the area | Wrong or omitted row selection in the region |
| Variance | VAR.S or VAR.P | Confirm the population or the sample first. | When the sample is mixed with the population, the result is too large or too small. |
| Proportion | Cell division, denominator can be fixed | The denominator is not zero | An error value occurs when the denominator is zero, or the fixed reference is misset |
| Difference value | Cell subtraction | The corresponding number of rows in two columns | The row is misplaced, or the result is displayed in date format |
The average value and range errors are mostly due to the wrong region selection; the variance errors are mostly due to the confusion between the sample and the population; the proportion and difference errors are mostly due to the reference mode or zero denominator. If you check these points separately, the investigation will be much faster.
# frequently asked questions (FAQ)
1. Is it unnecessary to use the online tool for finding formulas in excel?
Most statistics can be completed in the table with only a few functions, the data is left in the original table, and the results are automatically updated after modification. Online tools are suitable for temporary calculations and do not want to open the table, but if the data needs long-term maintenance, it is easier to write formulas directly.
2. What are the relevant and recommended excel range formulas?
The common way to write is MAX minus MIN, that is, = MAX (region)-MIN (region). A region can be a column, a row, or multiple non-adjacent regions, separating parameters with commas as needed.
3. Why is the result in the online tool for calculating excel variance formula different from that in the table?
In most cases, the two use different definitions of variance. The online tool may default to the total variance, while the VAR.S in the table is the sample variance, with an n-1 and n denominator difference. First confirm the nature of the data, and then unify the formula.
4. How to write the excel scale formula so that I can drag down without error?
The numerator uses a relative reference, such as B2; if the denominator is a fixed total, select the total area address and press F4 to fix, or just fix the line number, which will not move as you drag. Before dragging, take a look at whether the result of the second line is reasonable.
5. Why does the excel formula show dates or strange numbers?
This is usually a matter of cell format. If the two dates are subtracted, the result may be displayed as a date; simply change the result cell to a regular or numeric format.
# Summary
Averages, ranges, variances, proportions and differences can be written directly in the Excel table with functions or simple operations, and the results are updated synchronously when the data changes. Confirm whether the data is a number, a population or a sample before writing, and then do a spot check with counting and manual calculation. In this way, the statistical process is left in the table, which not only saves the repeated paste, but also makes it easy to check.
