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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920