Jamie from the UK sends in yet another formula to solve the Last Match formula from Episode 1073. Episode 1084 shows you how.
This blog is the video 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 blog is the video 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! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, you start out with massive amounts of data.
So, how we're going to analyze this.
Well, let's fire up a pivot table and see if you can solve this problem.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Yet, another solution to episode 1073.
This was where Sarah from the cattle farm in England, was trying to figure out.
the last match for this vehicle.
What was the last mileage?
Now, this time Jamie, also from England writes in with a really cool formula.
Let's talk about that formula.
It says hey, we're going to take the two arrays, two arrays.
First array, check everything in column A.
So, A$2, down to the road just above us A21 and see if that's equal to A22, that's the first array.
That's going to give us a whole bunch of TRUEs and FALSEs. Right.
And then we're going to multiply those TRUEs and FALSEs times the mileage over in B $2:B21, the row right above us.
Again, locking in only the dollar sign there.
So, think about that. Any time that the vehicle is equal to E14, we're going to get the mileage.
And if it's not equal to E14.
Well, that first thing is going to be FALSE which FALSE times when other number is always 0.
So, we're going to get either a bunch of zeroes or the mileage basically, only the mileage for E14.
Alright! Simple enough.
Now, Jamie said, We're going to assume that, this is in some sort of date sequence.
So, therefore the mileage for this fueling event, always has to be more than the mileage for the last fueling event.
So, really what we're looking for is the highest mileage.
So, those two arrays we're going to use =MAX of that nice array there, and of course we have to use [ Ctrl Shift Enter ] to get that.
So, 11726 and sure enough E14 allright there are 11728.
Too late, my eyes are not working tonight.
Copy that formula all the way up and the nice thing about this formula is that we don't get the N/As, when the vehicle has not been previously fueled.
So, they shows up as 0, a little bit more friendly than using the N/A.
Thanks to Jamie from the U.K. for sending that in.
I'm sure that Jamie already has an Excel master pin, has contributed to the podcast many times.
Want to thank you for stopping by, will see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, you start out with massive amounts of data.
So, how we're going to analyze this.
Well, let's fire up a pivot table and see if you can solve this problem.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Yet, another solution to episode 1073.
This was where Sarah from the cattle farm in England, was trying to figure out.
the last match for this vehicle.
What was the last mileage?
Now, this time Jamie, also from England writes in with a really cool formula.
Let's talk about that formula.
It says hey, we're going to take the two arrays, two arrays.
First array, check everything in column A.
So, A$2, down to the road just above us A21 and see if that's equal to A22, that's the first array.
That's going to give us a whole bunch of TRUEs and FALSEs. Right.
And then we're going to multiply those TRUEs and FALSEs times the mileage over in B $2:B21, the row right above us.
Again, locking in only the dollar sign there.
So, think about that. Any time that the vehicle is equal to E14, we're going to get the mileage.
And if it's not equal to E14.
Well, that first thing is going to be FALSE which FALSE times when other number is always 0.
So, we're going to get either a bunch of zeroes or the mileage basically, only the mileage for E14.
Alright! Simple enough.
Now, Jamie said, We're going to assume that, this is in some sort of date sequence.
So, therefore the mileage for this fueling event, always has to be more than the mileage for the last fueling event.
So, really what we're looking for is the highest mileage.
So, those two arrays we're going to use =MAX of that nice array there, and of course we have to use [ Ctrl Shift Enter ] to get that.
So, 11726 and sure enough E14 allright there are 11728.
Too late, my eyes are not working tonight.
Copy that formula all the way up and the nice thing about this formula is that we don't get the N/As, when the vehicle has not been previously fueled.
So, they shows up as 0, a little bit more friendly than using the N/A.
Thanks to Jamie from the U.K. for sending that in.
I'm sure that Jamie already has an Excel master pin, has contributed to the podcast many times.
Want to thank you for stopping by, will see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
