Jon asks how to calculate a winning or losing streak from baseball data. Episode 735 will show you how to build the formulas to calculate a win streak. Also, how to build a column showing Home or Away based on finding an @ sign in the opponent column.
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.
Today, we have a question sent in by John.
John downloaded some baseball data, he has the whole schedule here for a particular team and basically downloaded this from the web and wants to do some calculations with it now.
First question, he asked was.
He said, "Hey! We have this column over here, the opponent and if it was an away game, we have an @ sign.
If it was not, it just starts with the opponent.
How can I figure out a column to break out, the home versus the away games and so?" That's fairly simple.
It's an equal if function, so equal if the leftmost character of cell C2 comma 1 is equal to and then in quotes an add sign, then we'll say that it's away.
So, I'll put that in quotes, otherwise say that it's home.
Closing parenthesis and copy that down and you'll see that we should have a nice string.
Now, of either home or away and the next thing that John wanted to know and this was a little bit more difficult.
He said, "Hey! I want to keep track of streaks." You know, what's the current win streak or the current loss streak and so basically, what we're going to do, is we'll just start out will say that they started out with a 1 game win streak, and then it's a matter of looking over at column F.
So, equal if, if there was a win today.
So, if column F is equal to 1 then we're going to add 1 to the current streak.
So, 1 plus the number right above me otherwise we get reset back to zero and when I copy that down what we should see is that we will build up.
So here, there was a six-game win streak, and if you look at any particular date then as of that date, they were in a four-game win streak, a five game win streak and a six game win streak.
We should be able to just copy that formula right over to the lost streak.
Let's check and make sure that everything worked.
Yes! Now, we're looking at column G, instead of column F and adding the number right above us.
Copy that formula down, just double click the fill handle and we should be able to go through and see how many games the current streak either winning or losing is on any given day.
John for sending in that question and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
You
I'm Bill Jelen.
Today, we have a question sent in by John.
John downloaded some baseball data, he has the whole schedule here for a particular team and basically downloaded this from the web and wants to do some calculations with it now.
First question, he asked was.
He said, "Hey! We have this column over here, the opponent and if it was an away game, we have an @ sign.
If it was not, it just starts with the opponent.
How can I figure out a column to break out, the home versus the away games and so?" That's fairly simple.
It's an equal if function, so equal if the leftmost character of cell C2 comma 1 is equal to and then in quotes an add sign, then we'll say that it's away.
So, I'll put that in quotes, otherwise say that it's home.
Closing parenthesis and copy that down and you'll see that we should have a nice string.
Now, of either home or away and the next thing that John wanted to know and this was a little bit more difficult.
He said, "Hey! I want to keep track of streaks." You know, what's the current win streak or the current loss streak and so basically, what we're going to do, is we'll just start out will say that they started out with a 1 game win streak, and then it's a matter of looking over at column F.
So, equal if, if there was a win today.
So, if column F is equal to 1 then we're going to add 1 to the current streak.
So, 1 plus the number right above me otherwise we get reset back to zero and when I copy that down what we should see is that we will build up.
So here, there was a six-game win streak, and if you look at any particular date then as of that date, they were in a four-game win streak, a five game win streak and a six game win streak.
We should be able to just copy that formula right over to the lost streak.
Let's check and make sure that everything worked.
Yes! Now, we're looking at column G, instead of column F and adding the number right above us.
Copy that formula down, just double click the fill handle and we should be able to go through and see how many games the current streak either winning or losing is on any given day.
John for sending in that question and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
You
