"Go Zips!" The University of Akron mens soccer team wins a National Championship- the First University of Akron sports team to win a National Championship! In Episode #1305, Bill uses the new Excel 2010 Sparkline tool to analyze this season and last.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel form MrExcel podcast episode:1305, Win/Loss Sparklines.
Oh, hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. One of the new tools that we have in Excel 2010 is the Sparkline.
and now here on Insert, they have three different types of Sparklines.
Lines, columns and Win/Loss plan.
Win/Loss is one that I can never find a really good use for, except for sports I...
and I want to show the tale of two seasons here. So I select... well, the original data was W, L and T for Win/Loss and tie for the Akron Zips College soccer team and I'm gonna choose the data that converts that to 1's, 0's and negative ones. Negative ones are for losses 1's or any positive number is for wins and then 0 is for the ties.
So, we'll insert a Win/Loss Sparkline and the first thing they want to know is okay, where's the data? I already chose that. Where do you want the Sparkline to be?
Well, I want a single Sparkline here and that's gonna go in cell G3, so we click OK. All right, now, first thing, Sparklines are tiny by definition that's the way that Professor Tufte designed them but I find that Sparklines sometimes, take on a lot more meaning if we make them larger because so we can actually see what's going on there and also taller so let's maybe go to...
Yeah, that's look good. All right, first thing, we're going to change the marker color and what we have here is, we have high points and low points. I want to change the high points to green. So I go to Marker Color > High Point and choose nice bright green Marker Color, High point or a Low point and choose a red for that. Also, there's an Access drop down and they say show access to add a little line in there. Okay, so that's the 2009 season you see that they were undefeated until they got to the National Championship and lost. Now, let's build another Sparkline of the 2010 season.
So, we'll choose the Data, Insert > Win/Loss and put that one right here.
Click Ok and you see that because it column is wider the Sparkline is already wider. Let me change the marker colors again.
Low point.
Red and we'll make the height the same.
So here we add, that value. We'll press F4 to replicate the same height and Access, Show Access.
All right, now, let's kind of fill in a little bit of the story here. I think it helps to break the season up a little bit. So under the Insert, I want to go to Shapes and choose just a regular old line shape and we'll go back one, two, three, four, five games and draw a line in there.
All right and let's go to Shape Outline > Dashes.
That'd be nice a little dash line there and what this does is this breaks the, where the NCAA Tournament starts.
I'm gonna use Ctrl and drag to copy that and go left to where the Macs Tournament starts and then because after did so well this year, they were invited to a special preseason tournaments of those first two games out there in the left hand side. All right, so that Tournament, really good teams. They lost the second game. There was a tie here against Ohio State. No marker and then they say the thing that really helped the team was this stunning loss to Cleveland State, a team that Akron never should've lost to this.
They said that the guys have been on this huge run of wins and really gotten kind of complacent and that allowed them to snap things back in place. Win both games of the Mac tournament and then go on to win all five games of the NCAA tournament.
Becoming the first University of Akron team to ever win a national championship and a team sport. All right, so that just happened this past weekend.
Congratulations to the Akron Zips.
I wanted to take a look at this new feature in the Excel 2010 Win/Loss, to see if I could use that to tell an interesting story of what happened this season..
Well hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel form MrExcel podcast episode:1305, Win/Loss Sparklines.
Oh, hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. One of the new tools that we have in Excel 2010 is the Sparkline.
and now here on Insert, they have three different types of Sparklines.
Lines, columns and Win/Loss plan.
Win/Loss is one that I can never find a really good use for, except for sports I...
and I want to show the tale of two seasons here. So I select... well, the original data was W, L and T for Win/Loss and tie for the Akron Zips College soccer team and I'm gonna choose the data that converts that to 1's, 0's and negative ones. Negative ones are for losses 1's or any positive number is for wins and then 0 is for the ties.
So, we'll insert a Win/Loss Sparkline and the first thing they want to know is okay, where's the data? I already chose that. Where do you want the Sparkline to be?
Well, I want a single Sparkline here and that's gonna go in cell G3, so we click OK. All right, now, first thing, Sparklines are tiny by definition that's the way that Professor Tufte designed them but I find that Sparklines sometimes, take on a lot more meaning if we make them larger because so we can actually see what's going on there and also taller so let's maybe go to...
Yeah, that's look good. All right, first thing, we're going to change the marker color and what we have here is, we have high points and low points. I want to change the high points to green. So I go to Marker Color > High Point and choose nice bright green Marker Color, High point or a Low point and choose a red for that. Also, there's an Access drop down and they say show access to add a little line in there. Okay, so that's the 2009 season you see that they were undefeated until they got to the National Championship and lost. Now, let's build another Sparkline of the 2010 season.
So, we'll choose the Data, Insert > Win/Loss and put that one right here.
Click Ok and you see that because it column is wider the Sparkline is already wider. Let me change the marker colors again.
Low point.
Red and we'll make the height the same.
So here we add, that value. We'll press F4 to replicate the same height and Access, Show Access.
All right, now, let's kind of fill in a little bit of the story here. I think it helps to break the season up a little bit. So under the Insert, I want to go to Shapes and choose just a regular old line shape and we'll go back one, two, three, four, five games and draw a line in there.
All right and let's go to Shape Outline > Dashes.
That'd be nice a little dash line there and what this does is this breaks the, where the NCAA Tournament starts.
I'm gonna use Ctrl and drag to copy that and go left to where the Macs Tournament starts and then because after did so well this year, they were invited to a special preseason tournaments of those first two games out there in the left hand side. All right, so that Tournament, really good teams. They lost the second game. There was a tie here against Ohio State. No marker and then they say the thing that really helped the team was this stunning loss to Cleveland State, a team that Akron never should've lost to this.
They said that the guys have been on this huge run of wins and really gotten kind of complacent and that allowed them to snap things back in place. Win both games of the Mac tournament and then go on to win all five games of the NCAA tournament.
Becoming the first University of Akron team to ever win a national championship and a team sport. All right, so that just happened this past weekend.
Congratulations to the Akron Zips.
I wanted to take a look at this new feature in the Excel 2010 Win/Loss, to see if I could use that to tell an interesting story of what happened this season..
Well hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
