Go Back

Learn How to Calculate the Median from a Frequency Table in Excel

Learn How to Calculate the Median from a Frequency Table in Excel

Calculating the median from a frequency table is not as straightforward as many would want to think. Note that when working with a frequency table, the numbers might be repeating themselves a number of times and thus this might make it a little bit hard for us to get the median.

It is good to remember that the median is the middle number in a given list of numbers. If the total number is odd, then the median will be the middle one. But if the list is an even number, then we shall have to take n/2 + (n+1)/2 to get the median.

But how do you get the median when you have a frequency table?

This post provides an easy way on how one can get the median from a frequency table.

Figure 1. Final result

Procedure

  • For us to get the median of values in a frequency table, we first need to tabulate our table with the values and the number of times the values occur. This is as shown in figure 1 above where we have the values in column A and their frequencies in column B.
  • After that, we need to get the maximum number in the array so that we can have columns equal the total number of values in the list. This is as shown from cell D1:M10. Notice that the max value is 10.
  • We then have the following formula in cell D2;
    =IF(D$1>$B2,"",$A2)
  • We then copy this formula across the entire range, from cell D2:M11. This will bring each individual number and not as a frequency.
  • After that, we use the normal excel MEDIAN function to get the median of the array of values as shown below;
    =MEDIAN(D2:M11)

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment

avatar