Statistical Comparison of Two Data Sets Excel

In this article, I will discuss the statistical comparison of two data sets in Excel. At times, while working with spreadsheets, we have to compare data statistically. Luckily, Excel has some inbuilt functions to do the comparison between data sets.


Download the Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Key Method for Statistical Comparison of Two Data Sets in Excel

Excel Statistical Comparison of Two Data Sets Introduction

In our example, we will use two monthly sales data sets of Steel-cut oats and Rolled oats. By comparing statistically through excel, we will find out how sales of these two types of oats change over time. Besides that, we will show the sales graphically too. Furthermore, for the ease of our statistical comparison, we will find Mean, Standard Deviation, Coefficient of Variation, and Range for Steel-cut oats i.e. range (C5:C11) first.

Excel Statistical Comparison of Two Data Sets

Steps:

  • Initially, to get the Mean of Steel cut oats, type the following formula in Cell C12.
=AVERAGE(C5:C11)

Excel Statistical Comparison of Two Data Sets

Here, the AVERAGE function returns the arithmetic mean of dataset C5:C11.

  • Next, we will find out the standard deviation of dataset C5:C11. So, type the following formula in Cell C13.
=STDEV.S(C5:C11)

Excel Statistical Comparison of Two Data Sets

Here, the STDEV.S function estimates Standard Deviation based on  sample (ignores logical values and text in the sample)

  • Then, we will calculate the Coefficient of Variation of the dataset (C5:C11). The formula to calculate the CV is:

(Standard Deviation/Mean)*100

  • So, considering the above equation, type the below formula to get the Steel-cut oats’ sales:
=C13/C12

Excel Statistical Comparison of Two Data Sets

  • However, make sure you calculate the CV in percentage. To do that, select the corresponding cell (C14), go to Home > Number.

Excel Statistical Comparison of Two Data Sets

  • Now try to keep the value within 1 decimal place, and hit OK.

Excel Statistical Comparison of Two Data Sets

  • After that, we will calculate the range of the data set (C5:C11). To calculate the range of the above-mentioned data set, here is our formula:
=MAX(C5:C11)-MIN(C5:C11)

The MAX function returns the largest value of the dataset C5:C13. And, the MIN function returns the smallest value of that range. Lastly, by subtracting these minimum values from the maximum one, we will get the Range of the Steel-Cut Oats.

  • Finally, drag down the Fill Handle (+) tool to copy all the formulas to calculate the Mean, STD Deviation, CV, and Range of the Rolled oats data set.

Statistical Comparison Between Data Sets in Excel

Let’s compare the data sets depending on the result we got from the above calculation.

Mean: Mean is the arithmetic average of a dataset. And, from the above calculation, we can see that Rolled oat’s sales Mean is greater than that of the Steel cut one’s. That means, over time, the sales of Rolled oats are greater than the other one.

Standard Deviation: In statistics, the standard deviation is a measure of the amount of variation or dispersion of a set of values. A low standard deviation indicates that the values tend to be close to the mean of the set, while a high standard deviation indicates that the values are spread out over a wider range. Here, from our result standard deviation is greater for Rolled oats. Thus, this indicates the sales values of Rolled oats are spread out over a wider range than those of the Steel-cut oats.

CV: The coefficient of variation (CV) is a relative measure of variability that indicates the size of a standard deviation to its mean. From our above calculation, we can see that the CV of Steel cut oats is slightly higher than that of Rolled oats. Consequently, we can summarize that sales values of Rolled oats are more consistent compared to Steel-cut ones.

Range: In statistics, the range of a set of data is the difference between the largest and smallest values. It is evident from the datasets that Rolled oats have a higher range. This result indicates that, for some months, fluctuation of the sales of Rolled oats is higher than those of the Steel cut ones.


Conclusion

In the above article, I have tried to discuss the statistical comparison method elaborately. Hopefully, this method and explanation will be enough to solve your problems. Please let me know if you have any queries.


Related Articles

How to Calculate/Find Mean and Standard Deviation in Excel

Data Analysis in Excel – Learn 10 Common Statistical Terms

Running Average: How to Calculate Using Excel’s Average(…) Function

How to Use Scenario Manager in Excel [with Examples]

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo