A dueling Excel podcast where Mike and Bill take a look at how to sort using a formula. While most people would use RANK and COUNTIF, Episode 1025 shows you how to use LARGE and ROW or ROWS.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! I'm Bill Jelen, from MrExcel.com, I've got a cool Excel tip for you, today.
Hey! This is Mike Gel Girvin.
Excel Is Fun on You-Tube.
I have a different way to do that.
It's another dueling Excel podcast.
I'm Bill Jelen.
Thanks to George Wood for that great theme.
Hey! Today we want to learn, how to sort with a formula.
Now, I always go with rank, and COUNTIF but we're going to use a great formula from the book, Excel Gurus Gone Wild.
That's going, to solve this.
Now, first of all let me just hit you with an easy question, how do we find the largest value in that range?
Answer: Max of course, everyone uses max great way to figure out the largest value in the range and we see that that is 19 but what if we want this second largest value.
Ohh hey! There's another great function called LARGE.
So, here i'm going to put in the numbers 1, 2, 3, 4 and we'll see how this works =LARGE.
The way the LARGE works is, we point to our range right there [ F4 ], and ask for the first largest value, which is the exact same as max.
But, as we copy that down and ask for the second, third, fourth largest values.
It returns the second, the third and the fourth an array and the beautiful thing is it has no problem with ties, which the rank function does.
So, the large function is going to be our key to solving this problem.
I'm going to enter a formula here.
I'm going, to say i want the =Large.
Equal large of this array.
Now, make sure to hit the [ F4 ] key to lock that down and then i don't want to have to type the number one, the number 2, the number 3, the number 4.
So, I'm going to use my old trick ROW of A1.
Row of A1, is a way of typing the number one and as that gets copied down.
It's gonna ask for the row of B1 which is 2, and then 3 and then 4 and then 5.
I'll press [ ctrl + enter ] just enter that formula all the way down and there is our array of sorted values.
Cool! Now, that's if we need to go high to low.
If you need to go low to high then equal small.
=SMALL, that whole range again press the [ F4 ] key and the Row of A1 [ ctrl + enter ] and there's our values high to low, low to high.
Great trick.
Now, we'll throw this over the wall to Mike.
So, what Mike has for us.
Mike: Thanks MrExcel.
Hey! That's a pretty great formula, right there.
Anything from that Gurus Book is awesome.
Hey! I'm going to use the same formula but just do a slight variation.
I'm going to click right here.
We'll do large to small, =Large.
I'm going to get that same range control [ shift + down ] or [ F4 ] comma and instead of using ROW.
I'll use ROWS with an 'S', which counts the number of total rows and I'm sitting in E2.
So, i'm going to type 'e' dollar sign ($) 2 colon (:) e 2, close parentheses.
Right now, ROWS will say how many rows are there 2 to 2, but this is locked.
This one is not, as this expands it will increasingly get a larger count for rows.
I'm going to [ ctrl + enter ] double click and send it down.
Now, we can come right here and easily see that E2 to E4.
This was a relative cell reference which expanded the range.
So, now it's asking the question, how many rows between 2 and 4?
There are 3 which delivers 3 other then the third largest.
Now, for the small column.
I'm just going to copy this right here, paste it right here, and then change this LARGE to a SMALL.
By the way in 2007 as soon as you type a certain number of letters, if you see that your function is highlighted in blue.
You can just hit [ tab ] and it puts it in.
[ ctrl + enter ] and by the way this ROWS right here, you can see there was no dollar sign in from the G.
There was in front of the 2.
So, when we copied it over here and move to G2 but it's still locked going down and [ Ctrl enter ] and then double click and send it down.
Now, we have our two formulas so example here we use in ROWS, with an 'S' and ROW.
One of the advantages and disadvantages of each.
The advantage to ROWS, this range is referring to the range where the formula is.
So, if you were to use an external cell reference like A1.
If you deleted A1 or inserted a row above row 1, the formula would not work.
ROWS can handle that so advantage is it can handle the fact that a row might be inserted or a cell might be deleted.
Let's just go take a look here, I'm going to right-click [ delete ] that cell A1, we get a reference error because reference error means...
And we shifted it to the left.
Reference means that cell has been deleted.
I'm going to [ ctrl + z ], not only that but if we right-click [ insert ] then we get a NUM error because there is no Number 14 item in this list, that only has 13.
Now, I'm going to [ ctrl + Z ].
There's an easy way to fix that and that is simply to prevent people from deleting cells or inserting rows or deleting rows.
Go to review and then protect sheet and then click [ ok ].
Now, you cannot write, you cannot insert or delete.
So, that would fix that problem.
I'm going to go ahead and unprotect this.
So, advantage of ROWS is that it can deal with inserting columns or deleting cells.
Ah! But, the disadvantage is that this formula right here down at the bottom is looking through 2 to 14, 13 cells and as MrExcel just talked about in podcast 1014 and 1011.
Calculation speed matters especially for large spreadsheets.
So, boom!
The disadvantage is that that takes longer to calculate.
Especially, if you have a huge list.
So, advantage can deal with inserting rows, disadvantage calculating speed might be slow.
Advantage here calculating speed, boom!
Just one cell, calculating speed fast.
Disadvantage is, it can't deal with inserting rows.
All right! We'll see you next time.
MrExcel: Mike!
Excellent trick, using ROWS instead of ROW, and I hear what you're saying that you think that would take longer because ROWS has to evaluate a whole bunch of rows.
But, I started to think about that the LARGE function has to evaluate those same range of rows and actually look at all the values.
I mean ROWS is really doing a straight out subtraction or you know, from the end to the beginning and it turns out that ROWS vs ROW.
Now, when I actually ran it using my speed macro.
My method using ROW took 57.4 seconds.
Your method using ROWS 57.2 actually, faster.
Now, I think that has to be around here but let's just call them equivalent.
So, point to Mike.
Great way to avoid the problem with someone inserting or to leading a row, near the top of the spreadsheet using ROWS instead.
Hey! Have a great weekend.
Catch you next time for another dueling Excel podcast from Mike, Bill.
Hey! This is Mike Gel Girvin.
Excel Is Fun on You-Tube.
I have a different way to do that.
It's another dueling Excel podcast.
I'm Bill Jelen.
Thanks to George Wood for that great theme.
Hey! Today we want to learn, how to sort with a formula.
Now, I always go with rank, and COUNTIF but we're going to use a great formula from the book, Excel Gurus Gone Wild.
That's going, to solve this.
Now, first of all let me just hit you with an easy question, how do we find the largest value in that range?
Answer: Max of course, everyone uses max great way to figure out the largest value in the range and we see that that is 19 but what if we want this second largest value.
Ohh hey! There's another great function called LARGE.
So, here i'm going to put in the numbers 1, 2, 3, 4 and we'll see how this works =LARGE.
The way the LARGE works is, we point to our range right there [ F4 ], and ask for the first largest value, which is the exact same as max.
But, as we copy that down and ask for the second, third, fourth largest values.
It returns the second, the third and the fourth an array and the beautiful thing is it has no problem with ties, which the rank function does.
So, the large function is going to be our key to solving this problem.
I'm going to enter a formula here.
I'm going, to say i want the =Large.
Equal large of this array.
Now, make sure to hit the [ F4 ] key to lock that down and then i don't want to have to type the number one, the number 2, the number 3, the number 4.
So, I'm going to use my old trick ROW of A1.
Row of A1, is a way of typing the number one and as that gets copied down.
It's gonna ask for the row of B1 which is 2, and then 3 and then 4 and then 5.
I'll press [ ctrl + enter ] just enter that formula all the way down and there is our array of sorted values.
Cool! Now, that's if we need to go high to low.
If you need to go low to high then equal small.
=SMALL, that whole range again press the [ F4 ] key and the Row of A1 [ ctrl + enter ] and there's our values high to low, low to high.
Great trick.
Now, we'll throw this over the wall to Mike.
So, what Mike has for us.
Mike: Thanks MrExcel.
Hey! That's a pretty great formula, right there.
Anything from that Gurus Book is awesome.
Hey! I'm going to use the same formula but just do a slight variation.
I'm going to click right here.
We'll do large to small, =Large.
I'm going to get that same range control [ shift + down ] or [ F4 ] comma and instead of using ROW.
I'll use ROWS with an 'S', which counts the number of total rows and I'm sitting in E2.
So, i'm going to type 'e' dollar sign ($) 2 colon (:) e 2, close parentheses.
Right now, ROWS will say how many rows are there 2 to 2, but this is locked.
This one is not, as this expands it will increasingly get a larger count for rows.
I'm going to [ ctrl + enter ] double click and send it down.
Now, we can come right here and easily see that E2 to E4.
This was a relative cell reference which expanded the range.
So, now it's asking the question, how many rows between 2 and 4?
There are 3 which delivers 3 other then the third largest.
Now, for the small column.
I'm just going to copy this right here, paste it right here, and then change this LARGE to a SMALL.
By the way in 2007 as soon as you type a certain number of letters, if you see that your function is highlighted in blue.
You can just hit [ tab ] and it puts it in.
[ ctrl + enter ] and by the way this ROWS right here, you can see there was no dollar sign in from the G.
There was in front of the 2.
So, when we copied it over here and move to G2 but it's still locked going down and [ Ctrl enter ] and then double click and send it down.
Now, we have our two formulas so example here we use in ROWS, with an 'S' and ROW.
One of the advantages and disadvantages of each.
The advantage to ROWS, this range is referring to the range where the formula is.
So, if you were to use an external cell reference like A1.
If you deleted A1 or inserted a row above row 1, the formula would not work.
ROWS can handle that so advantage is it can handle the fact that a row might be inserted or a cell might be deleted.
Let's just go take a look here, I'm going to right-click [ delete ] that cell A1, we get a reference error because reference error means...
And we shifted it to the left.
Reference means that cell has been deleted.
I'm going to [ ctrl + z ], not only that but if we right-click [ insert ] then we get a NUM error because there is no Number 14 item in this list, that only has 13.
Now, I'm going to [ ctrl + Z ].
There's an easy way to fix that and that is simply to prevent people from deleting cells or inserting rows or deleting rows.
Go to review and then protect sheet and then click [ ok ].
Now, you cannot write, you cannot insert or delete.
So, that would fix that problem.
I'm going to go ahead and unprotect this.
So, advantage of ROWS is that it can deal with inserting columns or deleting cells.
Ah! But, the disadvantage is that this formula right here down at the bottom is looking through 2 to 14, 13 cells and as MrExcel just talked about in podcast 1014 and 1011.
Calculation speed matters especially for large spreadsheets.
So, boom!
The disadvantage is that that takes longer to calculate.
Especially, if you have a huge list.
So, advantage can deal with inserting rows, disadvantage calculating speed might be slow.
Advantage here calculating speed, boom!
Just one cell, calculating speed fast.
Disadvantage is, it can't deal with inserting rows.
All right! We'll see you next time.
MrExcel: Mike!
Excellent trick, using ROWS instead of ROW, and I hear what you're saying that you think that would take longer because ROWS has to evaluate a whole bunch of rows.
But, I started to think about that the LARGE function has to evaluate those same range of rows and actually look at all the values.
I mean ROWS is really doing a straight out subtraction or you know, from the end to the beginning and it turns out that ROWS vs ROW.
Now, when I actually ran it using my speed macro.
My method using ROW took 57.4 seconds.
Your method using ROWS 57.2 actually, faster.
Now, I think that has to be around here but let's just call them equivalent.
So, point to Mike.
Great way to avoid the problem with someone inserting or to leading a row, near the top of the spreadsheet using ROWS instead.
Hey! Have a great weekend.
Catch you next time for another dueling Excel podcast from Mike, Bill.
