From the course: Excel: Advanced Formulas and Functions
MEDIAN and MODE
- [Instructor] Let's look at some ways that Excel can help us get better insight into our data. Here we have a list of donations in column B. Okay? All the way down here to row 59. Great. One common thing that we do is we get the average, or the arithmetic mean. So let's do that, equals average, and double click. Now the numbers, and then we go over to this table and wait for that arrow and highlight that column and enter. So the average of all of these donations is 81 dollars and 75 cents. Now, the median, equals median. And it says what? Returns the median are the number in the middle of the set of given numbers. Double click. What our numbers over here this column and enter the median is 28 dollars. We can see that this way. I'm going to go over to this column and I'm in the data tab. I'm going to sort this A send in. And first I'm going to, wait for that arrow, highlight the entire column, look down in the corner, and it says we have a count of 57 values. Now I'm going to highlight this sale. Now I'm going to scroll down to the 28th value. Okay. There's a 28 on row 31. And there are 28 values above it and 28 values below it. Making 28 truly the middle. Okay. And that's very different from what we're calling our average. So let's look at the mode. Equals mode. Now notice the mode function has that triangle telling us that it's a compatibility function and we should avoid using it. And if you haven't seen that video already please go check it out so that you can get a better understanding of what's happening here. All right, so we've got mode mult and mode single. In this case we want mode single. And what is mode going to do? Return a vertical array of the most frequently occurring our repetitive values in a range of data. All right, so I'm going to click single. And then we want the column and enter. Okay, this is telling us that we have more two dollar donations than other donations. So we can look here and see that we've got seven two dollar donations, four five dollar donations. Scroll down. See we've got, two 100 dollar donations. So that's what mode is telling us. But now here is why the mode function is a compatibility function and has been replaced by mode single and mode multiple. Here we go. Equals mode. We want multiple. All right. Grab this column and enter. Ah. So this is saying that, yes, we have seven two dollar donations, but we also have seven 20 dollar donations, and seven 50 dollar donations. So all of these are telling us different stories about this list of donations. And we also see why it's helpful that we did mode multiple versus mode single. It also tells you how much you need to know your data, because mode single might be fine for what you deal with. Depends, depends on the context. So what do we see? The average, the overall, the values divided by the count would be 81 dollars, 75 cents. But, the median is telling us that exactly half of our donations coming in are above 28 dollars, and half are below 28 dollars. Mode multiple tells us when we get a new donation coming in it could be anything, but it's likely to be two dollars, 20 dollars, or 50 dollars. Now let's change some data and see what happens. What if this two dollars was actually a typo and should have been 200? Enter. All right, so now our median has moved. Both of our modes have adjusted. The average has adjusted, and it's telling us a different story. So there's your average, your median, and two types of mode.
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Download courses and learn on the go
Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.