Excel Formulas for get Average with MEDIAN or MODE Functions
The term "average" often refers to the arithmetic mean, which is the result of dividing the sum of numbers by the count of those numbers. This is how the AVERAGE function works. However, in Excel, you can also calculate two other types of averages: MEDIAN and MODE, which represent the middle value and the most frequent value in a data set, respectively.
Examples of Finding the Median, Mode, and Arithmetic Mean
Below is a list of 20 students with the grades of their exams. Suppose we want to analyze their grades using all three types of averages in Excel:
- Arithmetic mean.
- Median.
- Mode - the most frequent value.
Based on the calculated values, we can draw conclusions about the data.
The three formulas used to calculate the arithmetic mean, median, and mode in the data range displayed in the image are:
- =AVERAGE(B2:B21)
- =MEDIAN(B2:B21)
- =MODE(B2:B21)
The result of these formulas provides three different averages for the same group of numbers.
How to Find the Average in Excel Using the AVERAGE Function
The arithmetic mean of the grades is 85.1, the median is 90.5, and the most frequent grade is 93. The arithmetic mean is calculated using the AVERAGE function, which sums all the values in the specified range and divides by the count of those values. Comparing the median and the most frequent grade to the arithmetic mean offers additional insights into the data.
Formula for Calculating the Median in Excel Using the MEDIAN Function
The median is calculated using the MEDIAN function. If all grades are sorted in ascending order, the MEDIAN function returns the value in the middle of the list. Since the total number of grades is even, the list does not have a single middle number. In this case, the function returns the arithmetic mean of the two values closest to the center. As shown in the image, after sorting, the numbers 90 and 91 are closest to the center:
Therefore, the MEDIAN function returns 90.5 as the result. What does this mean?
A large difference between the arithmetic mean and the median indicates that the data is unevenly distributed. For example, in this case, there is a significant gap between the highest and lowest grades. If there is an outlier (a very high or very low value), it can strongly influence the arithmetic mean but not the median!
Formula for Calculating the Mode in Excel Using the MODE Function
The most frequent value is found using the MODE function. This function returns the grade that was most commonly received by the students. In the first image, the frequency of each grade is displayed next to it. The grade of 93 appears four times, making it the most frequent. If each value in the data set appears the same number of times (for example, all values are unique or have the same frequency), the MODE function will return the #N/A! error. If more than one value appears the same number of times (for instance, if 91 appeared four times, like 93), the function will return the first value it finds.
The MODE.MULT Function for Returning an Array of Mode Values in Excel
Starting with Excel 2010, two new functions were introduced for finding the most frequent values: MODE.SNGL and MODE.MULT. The MODE.SNGL function works similarly to the original MODE function, returning the most frequent value if there is more than one. The MODE.MULT function, however, returns an array of all the most frequent values.
Below is an example where the list contains two most frequent numbers (2 and 4). Both numbers appear twice in the list. Since MODE.MULT returns an array of values, you need to prepare a corresponding range of cells to hold the results. The image shows that three cells have been prepared, even though the MODE.MULT function only returns two values in this case. If the selected range of cells is larger than the array of results, the remaining cells will be filled with the #N/A! error.
Download examples of formulas for calculating averages using MEDIAN and MODE functions in Excel 
The MODE.MULT formula is an array formula, which requires pressing CTRL+SHIFT+Enter (instead of just Enter) to execute. If done correctly, curly brackets {} will appear around the formula in the formula bar, indicating that an array formula has been entered.
