Another Dueling Excel podcast. Bill and Mike discuss formulas to grab the last entry in a row. Episode 995 shows you two methods for solving the problem.
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 and Excel Is Fun on YouTube.
I have a different way to do that.
Hey! Welcome back to the MrExcel netcast.
We have another great dueling podcast for you this week.
Now, this is sent in by Steve.
Steve from England.
Steve has a series of estimates or forecasts going out here and for each individual person.
They might update their forecasts repeatedly and what Steve needs is a formula here in column B, that's going to go out and grab the last value from that row.
So, in this case, it's grabbing from column E.
Here it's going to grab from column F.
Here is going to grab from column G and so on.
Of course as we add more estimates in it should automatically go out and grab that last one.
I'm going to use a function here called INDEX.
Now, the index function, when I first read about wasn't sure exactly how I was ever going to use it.
Equal index, we have to give it an array and array can be you know, cells in a row, cells in a column or cells in a rectangle.
In this case, the array is going to be these cells right here.
In this row, I'm going to go out just in case they had more estimates later or something like that and then it wants to know which row do you want in which column, do you want?
Well, row is easy because there's only one row in this array.
So, that's one simple and then which column, okay!
Now, here's where the tricky part is going to be, I'm going to put a dynamic value in here instead of saying that I want column 3.
I'm going to say that I want you to go out and count how many times and estimate appears in that range.
So, count how many things are there.
So, in this case there's three entries in that array.
It will give me the third value. Now, I have to close the INDEX function, press [ enter ] and we get 49050.
Now, here's the test if we go out and add a new forecast 55,000 BAM!
And instantly updates, so let's copy that dow and do a little test here 23100 that looks good.
29900 and because I'm smart enough to go a little bit further.
So, I'm gonna add some more estimates it continues to update.
So, that all looks good, all right!
So, that's the method I've used.
Let's see what Mike is going to come up with. Mike turn over to you.
Mike: Hey! Thanks MrExcel.
Hey! I like that index solution. That was a pretty good one the one I have here.
I think is not quite as good but it is a common approach.
I'm going to use the OFFSET function.
The OFFSET function will allow us to have starting position.
It will say how many columns from the starting position we want to go over to retrieve the value.
So, here I'm going to use offset and there's five arguments.
We're going to use the first three only.
The first one says reference, and it means hey, where do you want to start?
Where's the starting position for us?
It's B2 and then comma ROWS says how many rows up or down do you want to go from B2?
We don't want to go any, so you can put a zero or you can just leave it blank and type a comma we move two columns and now columns from B2 how many columns do you want to move over?
Well. we need to move one, two, three, four to get that value and bring it back here.
So, I'm going to use the same count that MrExcel did and get this whole range all the way out to to there.
Now I close parentheses on that and then close parentheses on the opposite, and that's it right.
Now, it'll start at B2 and go out one, two, three, four get that value and bring it back.
So, offset is retrieving a particular value from a cell.
I'm going to double click and send it down.
Now, one problem with the offset is that it's a lengthy indirect function.
It's a volatile function.
So, if you have a big spreadsheet, it's always recalculate whenever, Excel recalculates.
Regardless if any data has changed in this range here.
So, that's why I like the index more.
Here's another problem both with the index and the offset here.
If you, if the person entering this data forgets and leaves a blank here.
Notice, it's retrieving the wrong value.
Now, I want to show you a solution and this solution is from the MrExcel message board.
If you haven't been to the MrExcel message board, you're missing Excel Heaven.
The smartest people in the world hang out there, and this is a great solution.
Now, I want to copy this sheet over the way you do that is I'm going to point to the sheet hold [ ctrl ], click on the sheet and drag up.
Notice that plus sign that means it's copying it.
I'm going to let go of the mouse, but not [ ctrl ].
So, I let go of the mouse and boom!
It copies it over.
Now, I'm going to come back over here and put some value here I don't remember what it was.
Just so, we would retain that, this offset and index works great.
If there are no blanks, but if there's blanks oh!
We're going to have some trouble highlight and here's the solution we use the lookup.
So, lookup and if we ask the LOOKUP function, we give it a lookup value, and then ask it to look in this particular row right here.
Well, if we give it a number that's too big, it automatically goes to the end and when it can't find a number bigger than that it'll just take the last one and a one common approach at the MrExcel message board is to actually put the biggest number that Excel knows 9.
One, two, three, four, five, six, seven, eight, nine, ten, eleven, twelve, thirteen, fourteen.
So, it's nine point nine with fourteen 9s there and then we go 'e' scientific notation plus 307.
That's the biggest number that Excel knows and then we simply can put this range in here all the way to the end and close parenthesis.
Now, there's a couple advantages and obviously you don't have to put the biggest number in there.
You can put any number in fact you could put the MAX function there if you wanted this whole range.
But the advantage of this is it's going to pick up the blanks because we'll always pick up the last one and it's not volatile like the offset and this number is a static number.
So, it's not ever calculating.
So, this is actually the fastest calculating of all of them, [ ctrl + enter ] and then double click and send it down and let's see if it works.
So, we got the 4 2.
Let's see if you put in another number here, 15 sure enough.
They've got that 15, so it picked up.
The only other method you might use if you want is to do the max Equals max of all this if you didn't want to use that big number.
So, I have the max of that whole range in the cell down here, and then you could simply instead of using that big number you could go ahead and highlight it and click there [ F4 ], [ Ctrl enter ] double click and send it down.
Alright!
There you go. See you next trick MrExcel: I am laughing out loud.
Do you see how Mike gets me there.
He starts out by saying, "Wow! I have a solution, that's not gonna be as good as MrExcel's." And then starts into the whole offset thinking about two minutes into his video, he then hits me with nine point nine nine nine nine. Plus E. Plus 307.
I tricked for my own message board, clearly the best solution of them all.
But actually he got me, when he started out saying he was just going to use offset and then hits us with this amazing, amazing trick for those of you keeping score that's another one for Mike.
Hey!
I want to thank everyone for stopping by, we'll see you next time for another net cast from MrExcel and Excel Is Fun.
I've got a cool excel tip for you, today.
Hey, this is Mike Gel Girvin and Excel Is Fun on YouTube.
I have a different way to do that.
Hey! Welcome back to the MrExcel netcast.
We have another great dueling podcast for you this week.
Now, this is sent in by Steve.
Steve from England.
Steve has a series of estimates or forecasts going out here and for each individual person.
They might update their forecasts repeatedly and what Steve needs is a formula here in column B, that's going to go out and grab the last value from that row.
So, in this case, it's grabbing from column E.
Here it's going to grab from column F.
Here is going to grab from column G and so on.
Of course as we add more estimates in it should automatically go out and grab that last one.
I'm going to use a function here called INDEX.
Now, the index function, when I first read about wasn't sure exactly how I was ever going to use it.
Equal index, we have to give it an array and array can be you know, cells in a row, cells in a column or cells in a rectangle.
In this case, the array is going to be these cells right here.
In this row, I'm going to go out just in case they had more estimates later or something like that and then it wants to know which row do you want in which column, do you want?
Well, row is easy because there's only one row in this array.
So, that's one simple and then which column, okay!
Now, here's where the tricky part is going to be, I'm going to put a dynamic value in here instead of saying that I want column 3.
I'm going to say that I want you to go out and count how many times and estimate appears in that range.
So, count how many things are there.
So, in this case there's three entries in that array.
It will give me the third value. Now, I have to close the INDEX function, press [ enter ] and we get 49050.
Now, here's the test if we go out and add a new forecast 55,000 BAM!
And instantly updates, so let's copy that dow and do a little test here 23100 that looks good.
29900 and because I'm smart enough to go a little bit further.
So, I'm gonna add some more estimates it continues to update.
So, that all looks good, all right!
So, that's the method I've used.
Let's see what Mike is going to come up with. Mike turn over to you.
Mike: Hey! Thanks MrExcel.
Hey! I like that index solution. That was a pretty good one the one I have here.
I think is not quite as good but it is a common approach.
I'm going to use the OFFSET function.
The OFFSET function will allow us to have starting position.
It will say how many columns from the starting position we want to go over to retrieve the value.
So, here I'm going to use offset and there's five arguments.
We're going to use the first three only.
The first one says reference, and it means hey, where do you want to start?
Where's the starting position for us?
It's B2 and then comma ROWS says how many rows up or down do you want to go from B2?
We don't want to go any, so you can put a zero or you can just leave it blank and type a comma we move two columns and now columns from B2 how many columns do you want to move over?
Well. we need to move one, two, three, four to get that value and bring it back here.
So, I'm going to use the same count that MrExcel did and get this whole range all the way out to to there.
Now I close parentheses on that and then close parentheses on the opposite, and that's it right.
Now, it'll start at B2 and go out one, two, three, four get that value and bring it back.
So, offset is retrieving a particular value from a cell.
I'm going to double click and send it down.
Now, one problem with the offset is that it's a lengthy indirect function.
It's a volatile function.
So, if you have a big spreadsheet, it's always recalculate whenever, Excel recalculates.
Regardless if any data has changed in this range here.
So, that's why I like the index more.
Here's another problem both with the index and the offset here.
If you, if the person entering this data forgets and leaves a blank here.
Notice, it's retrieving the wrong value.
Now, I want to show you a solution and this solution is from the MrExcel message board.
If you haven't been to the MrExcel message board, you're missing Excel Heaven.
The smartest people in the world hang out there, and this is a great solution.
Now, I want to copy this sheet over the way you do that is I'm going to point to the sheet hold [ ctrl ], click on the sheet and drag up.
Notice that plus sign that means it's copying it.
I'm going to let go of the mouse, but not [ ctrl ].
So, I let go of the mouse and boom!
It copies it over.
Now, I'm going to come back over here and put some value here I don't remember what it was.
Just so, we would retain that, this offset and index works great.
If there are no blanks, but if there's blanks oh!
We're going to have some trouble highlight and here's the solution we use the lookup.
So, lookup and if we ask the LOOKUP function, we give it a lookup value, and then ask it to look in this particular row right here.
Well, if we give it a number that's too big, it automatically goes to the end and when it can't find a number bigger than that it'll just take the last one and a one common approach at the MrExcel message board is to actually put the biggest number that Excel knows 9.
One, two, three, four, five, six, seven, eight, nine, ten, eleven, twelve, thirteen, fourteen.
So, it's nine point nine with fourteen 9s there and then we go 'e' scientific notation plus 307.
That's the biggest number that Excel knows and then we simply can put this range in here all the way to the end and close parenthesis.
Now, there's a couple advantages and obviously you don't have to put the biggest number in there.
You can put any number in fact you could put the MAX function there if you wanted this whole range.
But the advantage of this is it's going to pick up the blanks because we'll always pick up the last one and it's not volatile like the offset and this number is a static number.
So, it's not ever calculating.
So, this is actually the fastest calculating of all of them, [ ctrl + enter ] and then double click and send it down and let's see if it works.
So, we got the 4 2.
Let's see if you put in another number here, 15 sure enough.
They've got that 15, so it picked up.
The only other method you might use if you want is to do the max Equals max of all this if you didn't want to use that big number.
So, I have the max of that whole range in the cell down here, and then you could simply instead of using that big number you could go ahead and highlight it and click there [ F4 ], [ Ctrl enter ] double click and send it down.
Alright!
There you go. See you next trick MrExcel: I am laughing out loud.
Do you see how Mike gets me there.
He starts out by saying, "Wow! I have a solution, that's not gonna be as good as MrExcel's." And then starts into the whole offset thinking about two minutes into his video, he then hits me with nine point nine nine nine nine. Plus E. Plus 307.
I tricked for my own message board, clearly the best solution of them all.
But actually he got me, when he started out saying he was just going to use offset and then hits us with this amazing, amazing trick for those of you keeping score that's another one for Mike.
Hey!
I want to thank everyone for stopping by, we'll see you next time for another net cast from MrExcel and Excel Is Fun.
