The basic formula for coefficient of variation

Coefficient of variation (CV) is the standard deviation of a dataset divided by its mean, usually shown as a percentage. In Excel, you calculate it by dividing the result of STDEV by the result of AVERAGE, then multiplying by 100.

The formula looks like this: =(STDEV(range)/AVERAGE(range))*100

For example, if your data is in cells A2 through A10, you would type =STDEV(A2:A10)/AVERAGE(A2:A10)*100 into an empty cell. Excel returns a single number — that is your coefficient of variation.

CV tells you how spread out your data is relative to the mean. A CV of 15% means the standard deviation is 15% of the average. A CV of 50% means the data is much more scattered. This matters when you are comparing datasets with different scales — CV lets you compare apples to apples even if one dataset has much larger numbers than the other.

Key Takeaways

  • Coefficient of variation is calculated as (standard deviation ÷ mean) × 100 in Excel using the formula =STDEV(range)/AVERAGE(range)*100.
  • Use STDEV for sample data and STDEVP for an entire population; the choice depends on whether your data represents a sample or the complete set.
  • CV is useful for comparing how consistent two datasets are, even when the numbers in those datasets are very different sizes.
  • You can build a small table with your data, mean, standard deviation, and CV in separate columns to see the calculation step by step.

When to use STDEV versus STDEVP

Excel offers two standard deviation functions: STDEV and STDEVP. The difference matters for your result.

Use STDEV when your data is a sample — a subset of a larger group. Use STDEVP when your data is the entire population you care about. STDEVP gives a slightly smaller number because it assumes you have all the data, not just a portion of it.

In practice, most business and research work uses STDEV because you are usually working with a sample. If you are tracking the test scores of 30 students in one class, that is your population and you would use STDEVP. If you are tracking the test scores of 30 students to estimate how all students at the school perform, that is a sample and you would use STDEV.

Your CV formula becomes either =(STDEV(range)/AVERAGE(range))*100 or =(STDEVP(range)/AVERAGE(range))*100 depending on which applies to your situation.

Building a step-by-step calculation table

Rather than typing one formula into a single cell, you can build a small table that shows each step. This makes it easier to check your work and to explain the calculation to someone else.

Set up four rows: one for the data range label, one for the mean (using AVERAGE), one for the standard deviation (using STDEV or STDEVP), and one for the CV. In the cell next to "CV", type =B3/B2*100 (where B2 is your mean and B3 is your standard deviation). Excel calculates the result.

This approach is especially useful if you are calculating CV for multiple datasets on the same sheet. You can copy the structure down and change only the data range in each AVERAGE and STDEV formula, then all your CV results appear in a single column for straightforward comparison.

Comparing variability across different datasets

CV is most useful when you have two or more datasets with different average values and you want to know which one is more consistent.

Imagine you track daily sales for two product lines over a month. Product A averages $5,000 per day with a standard deviation of $800. Product B averages $500 per day with a standard deviation of $150. The standard deviations look different, but which product is actually more variable?

Calculate CV for both. Product A's CV is (800/5000)*100 = 16%. Product B's CV is (150/500)*100 = 30%. Product B is more variable relative to its average, even though its standard deviation is smaller in absolute terms. CV strips away the scale difference and shows you the real comparison.

Put both datasets in your spreadsheet, calculate the mean and standard deviation for each, then calculate CV for each using the same formula structure. The dataset with the lower CV is more consistent.

Handling empty cells and zero values

Excel's AVERAGE and STDEV functions skip empty cells automatically, which is usually what you want. If you have a gap in your data, the functions ignore it and calculate based on the cells that contain numbers.

Zero values are treated as numbers, not as empty. If your data includes actual zeros (like "zero sales on that day"), AVERAGE and STDEV include them in the calculation. If you want to exclude them, you need to delete them or move them to a separate area before calculating.

Be careful with negative numbers. CV assumes your data makes sense as a distribution around a mean. If your data includes both large positive and negative numbers that nearly cancel out, the mean becomes very small and CV becomes very large or even misleading. In those cases, CV may not be the right measure for what you are trying to understand.

Common mistakes and how to fix them

The most common mistake is forgetting to multiply by 100. The formula =STDEV(A2:A10)/AVERAGE(A2:A10) gives you CV as a decimal (like 0.15), not a percentage. Adding *100 at the end converts it to 15%, which is easier to read and compare.

Another mistake is using the wrong STDEV function. If you use STDEV when you should use STDEVP (or vice versa), your CV will be slightly off. Check whether your data is a sample or a complete population, then pick the right function.

A third mistake is including headers or labels in your range. If your data starts in A1 with a label like "Sales" and the actual numbers start in A2, use =STDEV(A2:A10)/AVERAGE(A2:A10)*100, not A1:A10. Including the text in A1 causes an error.

Using CV to compare consistency across time periods

CV is helpful for tracking whether a process or product is becoming more or less consistent over time. Calculate CV for each month or quarter, then plot those CV values on a chart to see the trend.

If you are monitoring manufacturing quality, a rising CV might signal that your process is becoming less stable. A falling CV suggests improvements. This is more meaningful than looking at standard deviation alone, because it accounts for changes in the average output.

Create a column for each time period with its data, calculate the mean and standard deviation for each period, then calculate CV for each. A straightforward line chart with CV on the vertical axis and time periods on the horizontal axis shows whether consistency is improving or declining.

Frequently Asked Questions

What is the difference between standard deviation and coefficient of variation?

Standard deviation tells you how spread out your data is in absolute terms — in the same units as your data. CV expresses that spread as a percentage of the mean, so it lets you compare datasets with different scales. If one dataset is in dollars and another is in cents, CV makes them comparable.

Can coefficient of variation be negative?

No. CV is always zero or positive because it is based on standard deviation, which is always zero or positive. A CV of zero means all values in your dataset are identical. A CV of zero is rare in real data.

What is a "good" coefficient of variation?

There is no universal threshold. What counts as high or low depends on your field and what you are measuring. In manufacturing, a CV below 5% might be excellent. In sales forecasting, a CV of 20% might be acceptable. Compare your CV to historical data or to competitors in your field to judge whether it is good.

Should I use STDEV or STDEV.S and STDEVP or STDEV.P?

Excel updated its function names. STDEV.S is the newer name for STDEV (sample), and STDEV.P is the newer name for STDEVP (population). Both pairs work identically. Use whichever your version of Excel recognizes; newer versions prefer the .S and .P versions.

Can I calculate CV for data that includes negative numbers?

Technically yes, but the result may be misleading. If your data has both large positive and negative values that partially cancel out, the mean becomes small and CV becomes very large. In those cases, CV does not reflect what you actually want to measure. Consider whether a different measure of variability makes more sense for your situation.