How to Calculate the Coefficient of Variation in Excel?

Coefficient of Variation is a statistical term that is used to measure the dispersion of data points with the arithmetic mean of all the data points in the series. In mathematical terms, it is the ratio of standard deviation with the mean. The formula for the coefficient of variation can be expressed as:

where and denotes the standard deviation and arithmetic mean of all the data points in the series respectively.

The coefficient of variation is a dimensionless quantity and is often expressed in percentage. Intuitively, we can say the higher the value of the coefficient of variance the more the data points are dispersed around the mean. Since the coefficient of variation is a dimensionless entity, it can also be used to compare two completely different data series even though their arithmetic means differ significantly from one another. It makes it possible to compare two different entities whose scales of measurement do not match each other.

One real-world scenario where the coefficient of variation is used in calculating the risk-reward ratio in stock markets. The less value of the coefficient of variation signifies lesser volatility relative to the return generated. Hence, an investor can decide to take a position in such stocks/funds in which volatility is less.

In this article, we are going to read about the calculation of the coefficient of variation in Excel. Since Excel is an advanced tool that offers multiple pre-defined functions to perform statistical operations we can expect Excel to have one such pre-defined function for calculating the Coefficient of variation. But sadly it does not include any built-in function to perform this operation. Instead, we can have a workaround to complete this operation that we are going to learn in the next section. From the mathematical formula of the coefficient of variation, to calculate the coefficient of variation we require standard deviation and mean of all the points in the series.

Step 1: Calculate the standard deviation of all the points in the series. It can be calculated using any of the below given three functions based upon our requirements:

(i) STDEV(): Used for calculating the standard deviation of a general series.

(ii) STDEV.P(): Used for calculating the standard deviation of a population.

(iii) STDEV.S(): Used for calculating the standard deviation of a sample.

Step 2: Calculate the arithmetic mean of all the data points in the series using the AVERAGE() function.

Step 3: Calculate the ratio by dividing the standard deviation value by the mean value.