Jon has a follow-up question about his baseball spreadsheet. After calculating the win and loss streaks, he would like to find the longest streak, including the start and end dates. This seemingly simple question requires two completely useless functions. Once these functions are used in conjunction, they become very powerful. Episode 736 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.
Well on Friday we were talking about baseball.
John had sent in a question. Over the weekend he sent in a follow-up question.
He said ok, that's great I understand how to calculate the win streak in the last week, but now I want to figure out the longest streak when it started and when it ended.
All right so the longest streak, that's easy. We're gonna use the MAX function.
Basically say the MAX of H2 down to H163 and we'll see that the longest streakk of wins was 8 and the longest streak of losses was 4.
Now he said but then I want to figure out when that streak started and when it ended?
Well we're going to talk about a function today that when I first read about this function, I really could not understand why we would ever want to use it.
It's the MATCH function and match is similar to VLOOKUP.
But instead of returning something like the second or third or fourth column from the table, MATCH tells me what row number that's found on.
and when I initially heard this what row number?
Whoever calls up and asked. What row number is something on? So let's do an =Match.
We want to go find that 8 within H2 to H163 and we put a comma 0 that's saying that we're looking for an exact match, not the number just lower not the number just higher, but the exact match and it's going to tell us that it's on row 135 within that range.
Now since I asked for it to check through H2 to H163 row 135 within that range is really row 136.
But what do we do with that? I mean, I don't know what to do with 135.
John wanted to know the date when something occurred, so what we're going to do is we're going to ask for the INDEX of B2 to B163.
The INDEX function needs to know which row we want to return from that range.
So here's what we do. We edit the formula =INDEX(B2:B163) and then which row do we want. Well the result of the MATCH is the row.
Put a closing parenthesis at the end and that's the right answer. It's a date. We have to format it as a date, so I'll go and choose a date format.
And we'll see that that streak ended on 9/1.
Okay, so what we're going to do here to figure out the start date is we know that basically it was 8 games before the ending date, so I'm going to edit this formula with F2.
Select the text in the formula and Ctrl C to copy and now I'll paste that here to cell L3 Again, we'll format that as a date and now I'm going to edit the formula and we're using the INDEX function.
Say we want to find everything from B2 to B163 and which row do we want. Well instead of using the MATCH we're going to take the MATCH and subtract whatever value is up there in L2. We have to add 1 and it calculates that the streak started on 8/25.
Now let's go check that if we scroll down here to the bottom, we should see on August 25th, we had our first win and it did lead to an eight game streak.
Well there you have it. The MATCH function.
Again when I first heard of match what row number? does something happen?
I never could figure out exactly what that would be useful for but when combined with the INDEX function becomes a very very powerful tool.
Want to thank John for sending in that question.
I want thank you for stopping by. We'll see you next time for another netcast from MrExcel You
Well on Friday we were talking about baseball.
John had sent in a question. Over the weekend he sent in a follow-up question.
He said ok, that's great I understand how to calculate the win streak in the last week, but now I want to figure out the longest streak when it started and when it ended.
All right so the longest streak, that's easy. We're gonna use the MAX function.
Basically say the MAX of H2 down to H163 and we'll see that the longest streakk of wins was 8 and the longest streak of losses was 4.
Now he said but then I want to figure out when that streak started and when it ended?
Well we're going to talk about a function today that when I first read about this function, I really could not understand why we would ever want to use it.
It's the MATCH function and match is similar to VLOOKUP.
But instead of returning something like the second or third or fourth column from the table, MATCH tells me what row number that's found on.
and when I initially heard this what row number?
Whoever calls up and asked. What row number is something on? So let's do an =Match.
We want to go find that 8 within H2 to H163 and we put a comma 0 that's saying that we're looking for an exact match, not the number just lower not the number just higher, but the exact match and it's going to tell us that it's on row 135 within that range.
Now since I asked for it to check through H2 to H163 row 135 within that range is really row 136.
But what do we do with that? I mean, I don't know what to do with 135.
John wanted to know the date when something occurred, so what we're going to do is we're going to ask for the INDEX of B2 to B163.
The INDEX function needs to know which row we want to return from that range.
So here's what we do. We edit the formula =INDEX(B2:B163) and then which row do we want. Well the result of the MATCH is the row.
Put a closing parenthesis at the end and that's the right answer. It's a date. We have to format it as a date, so I'll go and choose a date format.
And we'll see that that streak ended on 9/1.
Okay, so what we're going to do here to figure out the start date is we know that basically it was 8 games before the ending date, so I'm going to edit this formula with F2.
Select the text in the formula and Ctrl C to copy and now I'll paste that here to cell L3 Again, we'll format that as a date and now I'm going to edit the formula and we're using the INDEX function.
Say we want to find everything from B2 to B163 and which row do we want. Well instead of using the MATCH we're going to take the MATCH and subtract whatever value is up there in L2. We have to add 1 and it calculates that the streak started on 8/25.
Now let's go check that if we scroll down here to the bottom, we should see on August 25th, we had our first win and it did lead to an eight game streak.
Well there you have it. The MATCH function.
Again when I first heard of match what row number? does something happen?
I never could figure out exactly what that would be useful for but when combined with the INDEX function becomes a very very powerful tool.
Want to thank John for sending in that question.
I want thank you for stopping by. We'll see you next time for another netcast from MrExcel You
