In today's Dueling Excel Podcast, with Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen, Mike and Bill look at methods to calculate the MODE and MEDIAN from data that is arranged in a Frequency Table.
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
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
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
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Bill Jelen: Hey, welcome back.
It's another dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen. Mike Girvin will be joining us.
This is Episode number 86 – Mode may be Median from a Frequency Chart.
Okay, well this question came in from YouTube and I've redacted the name because we're fairly sure that he's asking for the wrong thing.
He or she was asking for the wrong thing.
So let me show you what we have.
We have this frequency right here.
So the value 1 appear 10 times.
The value two appeared five times.
The value three appeared four times.
And the person is asking for a formula that will calculate the first and second medians.
Well, hang on.
There aren't two medians.
There's two modes so we first try to figure out a formula that would do the median and that's hard.
Yes, that's a tough one.
And of course, anytime I'm faced with a tough formula, what do I do?
I go straight to VBA.
So let's switch over to VBA.
And I wrote this nice little thing here called Unwind.
It goes through all of those rows – A16 to A24.
It figures out what the value is.
It figures out what the count is, and then goes for i=1 To ThisCount, and writes the value out to Column L. Why did I choose L?
It was just out there on the right hand side.
There was room, increments the counter.
And then once I have everything unwound, then it's simple to put in a median.
For the mode, there can be two, three, four modes.
If there's a tie, the mode is the value that appears most frequently.
So we're using an array formula – FormulaArray, MODE.MULT.
This is new in Excel 2010.
It has to return a vertical value, or you have to use the transpose function.
So I just gave it three cells.
If there's more than that, then we’re not going to get it.
If there's less than that, we're going to get an NA.
So L5 to Ctrl-1.
I could have done that nicer or more elegantly.
And then just kind of put some labels in here – Median, Mode 1, Mode 2, Mode 3 and Unwind.
So let's run this, and we'll switch back to Excel, scroll over.
So right here, you see the...
You can't see anything.
Let's roll over.
Here's the Unwind.
So there are 10, 1s.
Yes, and then if we scroll down, you'll see that all the other numbers are there the appropriate number of times.
Once I have this big long string, well then hey, life becomes really, really easy.
=MEDIAN and then this single array formula of MODE.
So 1 and 5 are the modes.
There is no third mode.
There's no third value tied.
Figuring the mode, I don't want to simplify what Mike has to do here.
I think figure out the mode is kind of easy because we look for the max and frequency, and then kind of do a VLOOKUP, and then that trick to do the second VLOOKUP.
I think to get the mode, to get the median from this, that would just... that would cause my head to fly off.
Of course, Mike will probably have some way to do it.
So Mike, let's see what you have.
Mike Girvin: Thanks, MrExcel, Man, that VBA is kind of nice.
It does it all in one fell swoop, meaning it gives you the median and the mode.
Now when I first got this problem, the mode, I could figure that out, but the median I just went...
I even told MrExcel, there's no way I'm doing a formula and then, of course, I had a moment of epiphany and here it was.
And it's not a crazy array formula.
It's actually stealing from MrExcel’s playbook where you have extra columns to help you make your calculation.
Now the first thing I need to know is the max.
What's the max number of items?
So there's 10.
And then I'm going to make the numbers 1 to 10 over here because I want I'm going to extract all the data because in some way, I need to unwind this, right.
So I'm going to put the numbers 1 to 10, right because the maximum number of extracts I could have going this way is 10.
I'm actually going to move this down one, maybe add some dark color there for the label, all right.
And then I'm going to do a simple formula here.
And what I'm going to say is, =IF.
Well, what’s the number of items I need to list this way?
Well, I'm going to start saying this, because this is the first one, the second one, the third one.
And when I copy the formula this way, I need the dancing ants to move but when I go down this column, I need it to lock so I’m going to hit F4 twice to lock the row, but not the column.
And I say, hey, anytime that is greater than ah, 10.
Now this one I need to lock going this way but I when I move it down, the dancing ants needs to move to the next frequency, so I'm going to hit F4 three times.
All right.
So any time this is greater than that, what do I need to show?
Nothing.
For example, when I get down to five, when it gets past this five here, then there'll be nothing out here, otherwise please show me this.
Same here, this is the value I want to unwind but going this way, I need it locked going down.
I need it not locked so I'm going to lock only the column reference, all right.
So that's relatively easy.
Ctrl Enter.
Copy it over to the 10 and then double click and send it.
And now I have my values and I can simply use median – the median built-in function.
= MEDIAN(C5:H8).
And median ignores text.
And there is a text ring, that little blank thing I put in there, but it will just flat out ignore it.
There is the median.
So that's for median.
Now this actually turns out to be easier than the mode.
I'm going to do the same thing.
I need to know the max because it's the frequency, right?
I need to somehow get a 1 and a 5, and put it over here because these are the ones that occur most frequently.
That's the definition of mode.
Once I do that, I need to count how many of those are.
So I'll use =COUNTIF(B5:B13,B1), the range, how many.
That's the whole range of frequencies, and that's the criteria.
So it should give me 2.
Now absolutely, we could do a wild array formula here because it's a straight lookup, and anytime you have duplicates, you have to either use a while array formula, which is complicated to create and may use a lot of resources, or use a helper column.
Now this one is pretty straightforward.
I'm going to say, =, and I need an indicator that says when I find something in this row that's 10.
So I'm going to say, =(B5=$B$1) anytime that's equal to that one right there, and then F4 to lock it.
Ctrl Enter.
I'll show you why I put the parentheses around it in just a second.
Double click and send it down.
Now when you do this, this is cool.
It tells us this row has the number I want, but there's still duplicates even if you have this column.
If you're going to do a formula over here, you'd still have to do an array formula, but watch this.
I'm simply going to plus the one above, =(B5=$B$1)+A4.
Now I've done videos recently on this helper column when you have duplicates like this.
And the last videos, I put a word here which means you have to do this inside of a SUM function, because the SUM function can't ignore text, but right now, I don't have anything there so it's just zero.
Ah, it turns from a true or false to a number anytime you do an operation right, so that's pretty handy, all right.
Now look at this.
We have lots of duplicates.
Here, they weren’t going to work if I was looking them up, but here we'll come to our advantage, because if we do, well first off, this is the number we need to get – that one and that one.
When we do, our lookup will say, do an exact match and it will skip all over the duplicates, and only take the first one.
But I do need a lookup value here, so I'm going to type 1, 2, 3, 4.
And now, I simply do a straight VLOOKUP.
I'm going to say, hey, =VLOOKUP(E4,$A$5:$C$13,3,0), look up this value comma within this whole table, first column, F4.
I need to return the item in the third column, 3.
And the trick to deal with all these duplicates here in this first column and only take the first one, is to say exact.
I'm going to put 0.
Now we're going to get a little NA there, and I definitely don't want to show that so I'm simply going to use a 2007 function, IFERROR.
Now in earlier versions, and I have a video here, you have to repeat the VLOOKUP twice and use IF but IFERROR, totally awesome.
It just takes the thing, and if you have an error, double quote or whatever you want there.
I’ll enter it and then copy it over, all right.
Now if for some reason this was a 10 here, right, so then it would list that there.
Ctrl Z, all right, median and mode.
I’ll throw you back to MrExcel.
Bill Jelen: Mike, you holy surprised me.
That was pretty, pretty wild.
Median, I love that one, building the little table, just a good way to go.
And then your equal one plus the one blank cell above, and then the VLOOUP to get the first and the second.
I've never thought of going that way.
That's a point to Excel Is Fun.
All right.
Hey, I want to thank everyone for stopping by.
We'll see you next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
It's another dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen. Mike Girvin will be joining us.
This is Episode number 86 – Mode may be Median from a Frequency Chart.
Okay, well this question came in from YouTube and I've redacted the name because we're fairly sure that he's asking for the wrong thing.
He or she was asking for the wrong thing.
So let me show you what we have.
We have this frequency right here.
So the value 1 appear 10 times.
The value two appeared five times.
The value three appeared four times.
And the person is asking for a formula that will calculate the first and second medians.
Well, hang on.
There aren't two medians.
There's two modes so we first try to figure out a formula that would do the median and that's hard.
Yes, that's a tough one.
And of course, anytime I'm faced with a tough formula, what do I do?
I go straight to VBA.
So let's switch over to VBA.
And I wrote this nice little thing here called Unwind.
It goes through all of those rows – A16 to A24.
It figures out what the value is.
It figures out what the count is, and then goes for i=1 To ThisCount, and writes the value out to Column L. Why did I choose L?
It was just out there on the right hand side.
There was room, increments the counter.
And then once I have everything unwound, then it's simple to put in a median.
For the mode, there can be two, three, four modes.
If there's a tie, the mode is the value that appears most frequently.
So we're using an array formula – FormulaArray, MODE.MULT.
This is new in Excel 2010.
It has to return a vertical value, or you have to use the transpose function.
So I just gave it three cells.
If there's more than that, then we’re not going to get it.
If there's less than that, we're going to get an NA.
So L5 to Ctrl-1.
I could have done that nicer or more elegantly.
And then just kind of put some labels in here – Median, Mode 1, Mode 2, Mode 3 and Unwind.
So let's run this, and we'll switch back to Excel, scroll over.
So right here, you see the...
You can't see anything.
Let's roll over.
Here's the Unwind.
So there are 10, 1s.
Yes, and then if we scroll down, you'll see that all the other numbers are there the appropriate number of times.
Once I have this big long string, well then hey, life becomes really, really easy.
=MEDIAN and then this single array formula of MODE.
So 1 and 5 are the modes.
There is no third mode.
There's no third value tied.
Figuring the mode, I don't want to simplify what Mike has to do here.
I think figure out the mode is kind of easy because we look for the max and frequency, and then kind of do a VLOOKUP, and then that trick to do the second VLOOKUP.
I think to get the mode, to get the median from this, that would just... that would cause my head to fly off.
Of course, Mike will probably have some way to do it.
So Mike, let's see what you have.
Mike Girvin: Thanks, MrExcel, Man, that VBA is kind of nice.
It does it all in one fell swoop, meaning it gives you the median and the mode.
Now when I first got this problem, the mode, I could figure that out, but the median I just went...
I even told MrExcel, there's no way I'm doing a formula and then, of course, I had a moment of epiphany and here it was.
And it's not a crazy array formula.
It's actually stealing from MrExcel’s playbook where you have extra columns to help you make your calculation.
Now the first thing I need to know is the max.
What's the max number of items?
So there's 10.
And then I'm going to make the numbers 1 to 10 over here because I want I'm going to extract all the data because in some way, I need to unwind this, right.
So I'm going to put the numbers 1 to 10, right because the maximum number of extracts I could have going this way is 10.
I'm actually going to move this down one, maybe add some dark color there for the label, all right.
And then I'm going to do a simple formula here.
And what I'm going to say is, =IF.
Well, what’s the number of items I need to list this way?
Well, I'm going to start saying this, because this is the first one, the second one, the third one.
And when I copy the formula this way, I need the dancing ants to move but when I go down this column, I need it to lock so I’m going to hit F4 twice to lock the row, but not the column.
And I say, hey, anytime that is greater than ah, 10.
Now this one I need to lock going this way but I when I move it down, the dancing ants needs to move to the next frequency, so I'm going to hit F4 three times.
All right.
So any time this is greater than that, what do I need to show?
Nothing.
For example, when I get down to five, when it gets past this five here, then there'll be nothing out here, otherwise please show me this.
Same here, this is the value I want to unwind but going this way, I need it locked going down.
I need it not locked so I'm going to lock only the column reference, all right.
So that's relatively easy.
Ctrl Enter.
Copy it over to the 10 and then double click and send it.
And now I have my values and I can simply use median – the median built-in function.
= MEDIAN(C5:H8).
And median ignores text.
And there is a text ring, that little blank thing I put in there, but it will just flat out ignore it.
There is the median.
So that's for median.
Now this actually turns out to be easier than the mode.
I'm going to do the same thing.
I need to know the max because it's the frequency, right?
I need to somehow get a 1 and a 5, and put it over here because these are the ones that occur most frequently.
That's the definition of mode.
Once I do that, I need to count how many of those are.
So I'll use =COUNTIF(B5:B13,B1), the range, how many.
That's the whole range of frequencies, and that's the criteria.
So it should give me 2.
Now absolutely, we could do a wild array formula here because it's a straight lookup, and anytime you have duplicates, you have to either use a while array formula, which is complicated to create and may use a lot of resources, or use a helper column.
Now this one is pretty straightforward.
I'm going to say, =, and I need an indicator that says when I find something in this row that's 10.
So I'm going to say, =(B5=$B$1) anytime that's equal to that one right there, and then F4 to lock it.
Ctrl Enter.
I'll show you why I put the parentheses around it in just a second.
Double click and send it down.
Now when you do this, this is cool.
It tells us this row has the number I want, but there's still duplicates even if you have this column.
If you're going to do a formula over here, you'd still have to do an array formula, but watch this.
I'm simply going to plus the one above, =(B5=$B$1)+A4.
Now I've done videos recently on this helper column when you have duplicates like this.
And the last videos, I put a word here which means you have to do this inside of a SUM function, because the SUM function can't ignore text, but right now, I don't have anything there so it's just zero.
Ah, it turns from a true or false to a number anytime you do an operation right, so that's pretty handy, all right.
Now look at this.
We have lots of duplicates.
Here, they weren’t going to work if I was looking them up, but here we'll come to our advantage, because if we do, well first off, this is the number we need to get – that one and that one.
When we do, our lookup will say, do an exact match and it will skip all over the duplicates, and only take the first one.
But I do need a lookup value here, so I'm going to type 1, 2, 3, 4.
And now, I simply do a straight VLOOKUP.
I'm going to say, hey, =VLOOKUP(E4,$A$5:$C$13,3,0), look up this value comma within this whole table, first column, F4.
I need to return the item in the third column, 3.
And the trick to deal with all these duplicates here in this first column and only take the first one, is to say exact.
I'm going to put 0.
Now we're going to get a little NA there, and I definitely don't want to show that so I'm simply going to use a 2007 function, IFERROR.
Now in earlier versions, and I have a video here, you have to repeat the VLOOKUP twice and use IF but IFERROR, totally awesome.
It just takes the thing, and if you have an error, double quote or whatever you want there.
I’ll enter it and then copy it over, all right.
Now if for some reason this was a 10 here, right, so then it would list that there.
Ctrl Z, all right, median and mode.
I’ll throw you back to MrExcel.
Bill Jelen: Mike, you holy surprised me.
That was pretty, pretty wild.
Median, I love that one, building the little table, just a good way to go.
And then your equal one plus the one blank cell above, and then the VLOOUP to get the first and the second.
I've never thought of going that way.
That's a point to Excel Is Fun.
All right.
Hey, I want to thank everyone for stopping by.
We'll see you next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
