Excel Functions: AVERAGE, MEDIAN, MODE

When we created a Box Plot recently, one of the measures was the MEDIAN.

  • For an odd set of numbers, the MEDIAN is the number in the middle of the set.
  • For an even set of numbers, the MEDIAN is the average of the two numbers in the middle.

MEDIAN is one of the central tendency functions, along with AVERAGE and MODE.

AVERAGE Function

If, like me, you’re not a statistician, you probably use the AVERAGE function, more often than you use MEDIAN or MODE.

  • The AVERAGE is the SUM of the numbers, divided by the COUNT of the numbers

MODE Function

Do you ever use the MODE function? I used it in my statistics class at university, but not much since then, and we won’t talk about how long ago that was!

  • The MODE function returns the most frequently occurring number in the set
  • If there aren’t any duplicate numbers, the result is an #N/A error
  • If there is a tie, the most frequent number that occurs first is the result


Below, you can see a couple of very simple examples of measuring a small set of numbers.

In the first example, the numbers are symmetrically distributed, as you can see in the COUNT chart.

The second chart shows that the AVERAGE, MEDIAN and MODE are the same.


In the next example, the numbers are NOT symmetrically distributed, as you can see in the COUNT chart.

The second chart shows that the AVERAGE, MEDIAN and MODE are different.


Use the Interactive Workbook

If you would like to play with the sample workbook, you can change the numbers in the interactive Excel workbook, shown below. There are two worksheets with number sets – one is symmetrically distributed, and the other is not.

To get the score counts, I used the FREQUENCY function.


You may also like...

1 Response

  1. Ryan says:

    I’m looking for some excel help – and I’ve come across your page. I couldn’t really google this problem so I’m hoping I can explain it and you might be able to help?

    I have a total number of items (lets say, 600 dollars). I then have a weekly number subtracted from that total, that is manually entered by the user (user spent 50 dollars that week). after 3 weeks of this, I then take an average of those numbers, 4 weeks, new average, 5 weeks, new average – and so on. I then add/subtract a set amount to that average. this becomes a “high”, “average”, “low”. so if the output of the average is “50”, the numbers calculated would be 65.00,50.00,45.00(high, average, low). I then use those numbers to forecast ahead So that I can answer a question like “in 3 weeks from now where will we be, how close will we be to having used up our original amount of money?” This gives me then a “high” “normal” “Low” number to forecast with. so I can say things like “well, if more than average is spent, we’ll be out of money in week 5, if the average amount is spent, week 7, and if we spend lower than what we have been, week 9”.

    The difficulty for me is this: I need that forecast to always be moving forward – only showing a forecast, never the past weeks forecasted amount. so in week 3 – it forecasts from week 4 – 7, in week 4, it forecasts 5 – 8, and so on. It needs to be a “rolling forecast”. It needs to be always moving forward because this is used in a graph where the past figures are a bar chart, and this is all done on a secondary axis.

    This essentially a burndown chart with a “cone of uncertainty” attached to it. I have it working but the forecasting part I’m asking about is highly manual – I’m trying to formula this if possible.

    Here’s a link to a visual example where this is entirely done with a line graph. Mine is more complicated in the data it gathers(different kinds of spending is also shown by using a filled bar chart), but the idea and intent is the exact same.
    http://alistair.cockburn.us/get/1851 – as you can see from the image, the high/average/low is always moving forward and is separate from “actual”

    can anyone help or point me in a certain direction?

Leave a Reply

Your email address will not be published. Required fields are marked *