Use the MEDIAN function to identify the middle value in a range of cells containing values. If the number of cells being tabulated is even, average the middle two cells. MEDIAN is often contrasted with AVERAGE. Use the MODE function to indicate the value that appears most often within a selected range.
- [Voiceover] In the workbook 05-Stat we've got the first worksheet called median mode. Both of those are functions, statistical functions. They have different uses. We can use them in the example data that we've got here. I'm looking at the data here. I want to know what the average compensation amount is. For an average, I can simply click column F. We see at the bottom 64,838. We can also do this by way of a function, equal, average, and that's commonly used. We'll get the same answer this way. The function median does something similar, but certainly not identical.
Median picks the middle value out of a list. Now behind the scenes as we're using median, we don't see what's happening, but if I click column F here, Excel will take all the data in column F and sort it and pick out the middle value. If there happened to be an even number of entries here, it will take the middle two and average those to come up with an answer. It's not uncommon to see the average of a list and a median compared side by side. Sometimes the difference is meaningful, sometimes not. An average can be distorted by an extremely high or an extremely low number.
Now way off to the right here, column Q, we've got some House Values, perhaps for a neighborhood and we might want to know what the median house value is there. Equal median, that's the middle value if these were sorted and we pick out the middle one of this list right here. There we are, 679,000. The average might be higher or might be lower, not always sure ahead of time. There it is. Now, if we raise the value of one of these houses, suppose this one right here.
What if this is 1.5 million. This goes up by a million. It's obviously going to change the average. Will it change the median? Median went up a bit. The median went up a bit, so did the average. Now depending on how these are changed, you can go in different directions. But I'm not suggesting that every time you use average you will use median and vice versa, but we see how it's working here. Another list over here. It could be more meaningful than median or an average, but once again, we could do either one. Equal median, left parenthesis. We're highlighting all these cells here.
Enter, that's the median value. Once again, comparing with average if we wish. Same general idea. A difference there and that too will change as we work with the numbers. And over time, depending upon how you work with sets of data, you might want to compare the two. In some situations, it's meaningful, other times not. Now, the word mode has a completely different meaning and it only works with numbers as does median. We want to know which Job Rating is most common here. The only entries we see here are entries one through five.
Equal mode. What's the most common number that we see in column G? Five is most common. We could adjust this. What's the most common number of dependents we see over in column B? We'll just change the reference here from G to B. The answer over there is two. We could verify this with accountive formula possibly, but mode does its job and this is the correct answer. And similarly, what's the most common number of years of service in this list here? Instead of looking in column B, we'll be looking in column D.
There it is. Possibly with numbers like this, if they were lower, we could use mode. Now we can use mode on this as well too, but if any numbers are repeated there, and that's possible, we might not get the answer here that we were expecting, but it looks as if 479 occurs more than the others. Now I see it twice there. I see it actually three times, so maybe it's there more than that. All we know is that it appears more than the others. We'd have to use other functions to see how often it appears. So I think you can see, based on using median, which we saw in previous examples and mode.
From time to time you need these functions to come up with the calculations as we saw them here.
- Understanding how the hierarchy of operators affects formula results
- Knowing when to use absolute vs. relative references
- Using Formula Builder for unfamiliar functions
- Displaying a worksheet's formulas and highlighting formula cells
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Creating and expanding nested IF functions; using AND, OR, and NOT with IF
- Looking up information with VLOOKUP, HLOOKUP, MATCH, and INDEX
- Analyzing data with the statistical functions: MEDIAN, MODE, etc.
- Using the power functions: COUNTIF, SUMIF, COUNTIFS, AVERAGEIFS, and more
- Calculating financial data such as payments
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Using array formulas and functions
- Referencing data in other cells and files
- Extracting information from Excel worksheets