Excel - Calculate MODE and MEDIAN from Frequency Table - Excel - Episode 1446

4,207 views · Published 11 October 2011 · 9:09 · Indexed 12 November 2026

Channel: MrExcel.com · 2011 · Education

Watch on YouTube

Microsoft Excel Tutorial: Methods to calculate the MODE and MEDIAN from data that is arranged in a Frequency Table.

Welcome to another exciting episode of Dueling Excel, where MrExcel and Excel Is Fun go head to head to solve your toughest Excel challenges. In this episode, we tackle the question of calculating the mode and median from a frequency table. While the question may seem straightforward, there are actually some tricky nuances that we will explore.

The question came in from a viewer on YouTube, and we have redacted their name to protect their privacy. They were asking for a formula to calculate the first and second medians, but upon further examination, we realized that they were actually looking for the mode. This may seem like a small mistake, but it actually changes the approach to solving the problem.

To start, we have a frequency table with values and their corresponding frequencies. The challenge is to find a formula that can calculate the mode, which is the value that appears most frequently, and the median, which is the middle value in a set of numbers. While finding the median can be difficult, we can use VBA to make the process easier. By creating a simple VBA code, we can "unwind" the frequency table and extract all the values, making it easier to calculate the median.

For the mode, we need to consider the possibility of multiple modes, as well as ties between values. To solve this, we use an array formula, specifically the MODE.MULT function, which is only available in Excel 2010 and newer versions. This function returns a vertical array, so we use the TRANSPOSE function to convert it into a horizontal array. By using a helper column and a VLOOKUP formula, we can easily find the first and second modes.

But Excel Is Fun has a different approach to solving this problem. Using a combination of formulas and helper columns, he is able to find the mode and median without using VBA. By creating a table with numbers 1 to 10 and using a simple IF formula, he is able to extract all the values from the frequency table. Then, by using the COUNTIF and VLOOKUP functions, he is able to find the mode and median, even when there are ties between values.

In the end, both approaches are effective in finding the mode and median from a frequency table. Whether you prefer using VBA or formulas, there is always more than one way to solve an Excel problem. We hope you enjoyed this episode of Dueling Excel and learned some new techniques for your own Excel challenges. Don't forget to subscribe to both MrExcel and Excel Is Fun for more exciting duels in the future. Thanks for watching!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Mode may be Median from a Frequency Chart
(00:12) Question from YouTube about calculating first and second medians
(00:23) Explanation of the frequency chart
(00:33) Formula for calculating first and second medians
(00:43) Clarification on the difference between median and mode
(00:53) Use of VBA to solve the problem
(01:17) Explanation of the MODE.MULT function in Excel 2010
(01:30) Running the VBA code and displaying the results in Excel
(01:59) Explanation of the helper column method for calculating median and mode
(02:51) Mike Girvin's solution using the helper column method
(03:15) Explanation of the helper column method for calculating median and mode
(03:25) Mike Girvin's solution using the helper column method
(05:00) Explanation of the COUNTIF function for calculating mode
(05:23) Calculation of median using the built-in function
(05:54) Calculation of mode using the helper column method
(08:36) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
array formula
calculation
count
COUNTIF function
dancing ants
duplicates
Episode 86
exact match
Excel
extra columns
formula
helper column
IF function
IFERROR function
labels
lock row/column
max number of items
Median
Mike Girvin
Mode 1
Mode 2
Mode 3
Mode may be Median from a Frequency Chart
MODE.MULT
NA error
transpose function
unwind
VBA
VLOOKUP function


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153124/

Dueling Excel Podcast #86...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]

and

More from this channel