How to Calculate Mean, Median, and Mode in Excel

Mean, median, and mode are three common measures of central tendency that describe the average or typical value of a set of data. They are useful for summarizing and comparing data, as well as for analyzing the distribution and variability of data.

In this article, we will explain the basic theory behind mean, median, and mode, and how to calculate them using Excel formulas. We will also provide a scenario to illustrate how to apply these concepts to real data, and show the results using an Excel table. Finally, we will discuss some other approaches to calculate mean, median, and mode in Excel, such as using built-in functions, pivot tables, and charts.

The Basic

Mean

The mean, also known as the arithmetic mean or average, is the sum of all the values in a data set divided by the number of values. It represents the balance point or center of mass of the data. The formula for calculating the mean is:

Mean=∑x/n​

where $x$ is a value in the data set, and $n$ is the number of values.

The mean is sensitive to outliers, which are extreme values that are much higher or lower than the rest of the data. Outliers can skew the mean and make it less representative of the data.

Median

The median is the middle value of a data set when it is sorted in ascending or descending order. It divides the data into two equal halves, such that 50% of the values are less than or equal to the median, and 50% of the values are greater than or equal to the median. The formula for calculating the median depends on whether the number of values is odd or even.

If the number of values is odd, the median is the value at the position $(n+1)/2$, where $n$ is the number of values. For example, if the data set is {2, 4, 5, 7, 9}, the median is the value at the position $(5+1)/2=3$, which is 5.

If the number of values is even, the median is the average of the two values at the positions $n/2$ and $(n/2)+1$, where $n$ is the number of values. For example, if the data set is {2, 4, 5, 7, 8, 9}, the median is the average of the values at the positions $6/2=3$ and $(6/2)+1=4$, which are 5 and 7. The median is then (5+7)/2 = 6.

The median is less sensitive to outliers than the mean, as it only depends on the middle values of the data. It can better reflect the typical value of the data when there are extreme values.

Mode

The mode is the most frequently occurring value in a data set. It represents the most popular or common value of the data. There can be more than one mode in a data set, or no mode at all. The formula for calculating the mode is not as straightforward as the mean or median, as it requires counting the frequency of each value and finding the maximum.

The mode is not affected by outliers, as it only depends on the frequency of the values. It can show the preference or trend of the data, but it may not be very informative if the data is evenly distributed or has multiple modes.

Procedures

To calculate the mean, median, and mode in Excel using formulas, we need to follow these steps:

1. Enter the data in a column or row of a worksheet, such as A1:A10.
2. To calculate the mean, use the formula =SUM(A1:A10)/COUNT(A1:A10), where A1:A10 is the range of the data. This formula will add up all the values in the range and divide by the number of values. Enter the formula in a cell, such as B1, and press Enter.
3. To calculate the median, use the formula =MEDIAN(A1:A10), where A1:A10 is the range of the data. This formula will sort the data and return the middle value, or the average of the two middle values if the number of values is even. Enter the formula in a cell, such as B2, and press Enter.
4. To calculate the mode, use the formula =MODE.SNGL(A1:A10), where A1:A10 is the range of the data. This formula will return the most frequently occurring value in the range, or #N/A if there is no mode. Enter the formula in a cell, such as B3, and press Enter.

Scenario

To illustrate how to calculate mean, median, and mode in Excel using formulas, let’s consider a scenario where we have a data set of 10 sales figures (in thousands of dollars) for a company in January 2024. The data is shown in the table below:

We want to calculate the mean, median, and mode of the sales figures to summarize and compare the performance of the company.

Using the procedures described above, we can enter the data in a column of a worksheet, such as A1:A10, and use the formulas =SUM(A1:A10)/COUNT(A1:A10), =MEDIAN(A1:A10), and =MODE.SNGL(A1:A10) in cells B1, B2, and B3, respectively. The results are shown in the table below:

The mean of the sales figures is 24.5, which means the average sales figure is $24,500. The median of the sales figures is 23.5, which means the middle sales figure is $23,500. The mode of the sales figures is 12, which means the most common sales figure is $12,000.

Other Approaches

Besides using formulas, there are other ways to calculate mean, median, and mode in Excel, such as using built-in functions, pivot tables, and charts.

Using Built-in Functions

Excel has some built-in functions that can calculate mean, median, and mode without using formulas. These functions are located in the Statistical category of the Formulas tab. Some of the functions are:

• AVERAGE: This function returns the mean of a range of values. It is equivalent to using the formula =SUM(range)/COUNT(range).
• MEDIAN: This function returns the median of a range of values. It is the same as using the formula =MEDIAN(range).
• MODE: This function returns the mode of a range of values. It has two variants: MODE.SNGL and MODE.MULT. The former returns the most frequently occurring value, or #N/A if there is no mode. The latter returns an array of all the modes, or #N/A if there is no mode.

To use these functions, we need to select a cell where we want to display the result, click the Formulas tab, click the Insert Function button, select the Statistical category, choose the function we want, enter the range of the data, and click OK.

For example, to calculate the mean of the sales figures using the AVERAGE function, we can select cell B1, click the Formulas tab, click the Insert Function button, select the Statistical category, choose the AVERAGE function, enter A1:A10 as the range, and click OK. The result will be 24.5, the same as using the formula =SUM(A1:A10)/COUNT(A1:A10).