When a baseball pitcher is in the game for 7 and 1/3 innings, the stats books show this as 7.1 innings. But to calculate ERA, you need this to calculate as 7.333. Is there a way in Excel to easily convert between 7.1 and 7.333? It turns out Excel has a built-in function for this!
Transcript of the video:
Bill: Hey welcome back, it's time for another Dueling Excel podcast, this is a great one today.
I'm Bill Jelen from MrExcel, will be joined by Mike Girvin from Excel Is Fun, this is our episode 132 - 10.2 Means 10 2/3 Innings!
Alright, great question sent in today, it's baseball season here in the United States.
In fact, I'm taking my nephew to the baseball game.
Tonight Major League Baseball, the pitchers earned run average is measured in third of an innings because there's three outs in an inning, but the stat sheets show it as 10.2 meaning ten and two thirds of an inning.
Is there a formula to show 2/3 as .2?
Mike, shameless promotion here, I had to write this book, I got to write this book, and this is the third edition of this book, and back in the first edition, back in 2007, they said you have to cover every Excel function, every one of them, and there's 400 and what?
68 of them now.
So you know, I had it I had to come up with a real-life use for every single Excel function, and because of that, I remember this amazing solution, and this was created for the stock traders, back in the days when stocks, were reported in eighths, and back then 10.4 meant 10 and 4/8, not 10 and 4/10.
So here's the number, and we want to show it in fractions, where each point one is a third.
This is unbelievable, a function to solve this exact problem, exactly like the person wants it.
And to go back, =DOLLARDE , that, comma, and thirds will go back the other way.
So they had two functions built in that do this exact thing.
Now they created it for stock traders, but hey, it works for baseball statisticians as well.
So all the Money Ball- the people who are really doing that Money Ball statistics, they have a solution here.
Now, let's assume that we didn't have those functions, a different way to go is =MOD of this number, ,1 , that gives us just the decimal portion, cuts off the integer and gives us just the decimal.
And let's show a few more decimal places here, alright, so we have our MOD there.
I'm going to use the TEXT function to take that answer, and show it in a fractional 0/3 Custom Number Format, which gets me the fractions of 0/3, 1/3, 2/3.
But we don't need the divided by 3, so I'm going to use the LEFT.
That ,1 , that gets me the portion that should appear after the decimal place, and so we will divide by 10, which gets us the .1 .2 and so on.
And then finally we just have to add back in the original number, the integer of that A9, and copy it down.
This point we have one too many decimals, so we'll go back, Decrease Decimal.
So there's the answer without using DOLLARFR or DOLLARDE.
If you want to go back the other way, same thing, I'll take the MOD, this ,1 which gives us the fraction, the part that goes after the decimal place.
Let's multiply that whole thing times 10, and then divide by 3, which will get us the .1 .2 .3 converted back to a fraction.
And then finally just the integer of the original value, and it converts it back.
Alright, so DOLLARDE and DOLLARFR, two old, old functions left over from the days when stock traders reported eighths, as 10.7 meant 10 and 7/8, works here for this baseball example.
Mike, let's see what you have!
Mike: Thanks MrExcel!
Ah, but we caught you this time!
MrExcel just created this super extra-long, unnecessarily long formula, just to disguise the genius of DOLLARFR, DOLLARDE.
Stock functions we're using for baseball, man, how cool is that!
So you know, I don't really have much choice here, because check this out: That formula right there is the formula I was going to use to go from the .1 .2 to the decimal, however for the decimal to the .1 .2, that long formula MrExcel does.
Basically we're going to do the same formula here, but instead of multiplying by 10 and dividing by 3, we just multiply by 3 and divide by 10.
So that will work.
but I'm never going to use these formulas again, here's how I'm going to do: I'm going to copy MrExcel, I'm just going to do it slightly a different way.
How about I start over here, =DO , down arrow, down arrow, TAB, left arrow, ,3 will be our denominator, CTRL+Enter.
CTRL+C, I'm going to come over here and CTRL+V, now I'm going to use the keyboard shortcut for Find and Replace, CTRL+H. I am going to search for FR, and replace with DE.
Yes, Find and Replace works on formulas, Replace All, click OK, click Esc, and no way!
MrExcel gets 10 points!
Bill: Hey, alright Mike, that was good, I love the Find and Replace over here with CTRL+H.
You know, I told Mike offline that the first time I recorded this, I figured out the DOLLARFR, and I could not figure out the way to go back and.
So, over on the right hand side, I actually used that long formula, I just had this nagging feeling that there was a way to go back.
It was actually when I was watching my first video to edit it, that I noticed here, in this tool tip, the DOLLARDE!
I always thought that was like somehow related to German Dollars or something like that.
Which, of course, is ridiculous because it's Deutschmarks.
But that's surprising "Oh, that's the one I needed right there", was right there in front of me the whole time.
It's funny how sometimes we see things in Excel and don't really focus on them at all, so DOLLARFR and DOLLARDE, I like that.
Well hey, I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and Excel Is Fun!
I'm Bill Jelen from MrExcel, will be joined by Mike Girvin from Excel Is Fun, this is our episode 132 - 10.2 Means 10 2/3 Innings!
Alright, great question sent in today, it's baseball season here in the United States.
In fact, I'm taking my nephew to the baseball game.
Tonight Major League Baseball, the pitchers earned run average is measured in third of an innings because there's three outs in an inning, but the stat sheets show it as 10.2 meaning ten and two thirds of an inning.
Is there a formula to show 2/3 as .2?
Mike, shameless promotion here, I had to write this book, I got to write this book, and this is the third edition of this book, and back in the first edition, back in 2007, they said you have to cover every Excel function, every one of them, and there's 400 and what?
68 of them now.
So you know, I had it I had to come up with a real-life use for every single Excel function, and because of that, I remember this amazing solution, and this was created for the stock traders, back in the days when stocks, were reported in eighths, and back then 10.4 meant 10 and 4/8, not 10 and 4/10.
So here's the number, and we want to show it in fractions, where each point one is a third.
This is unbelievable, a function to solve this exact problem, exactly like the person wants it.
And to go back, =DOLLARDE , that, comma, and thirds will go back the other way.
So they had two functions built in that do this exact thing.
Now they created it for stock traders, but hey, it works for baseball statisticians as well.
So all the Money Ball- the people who are really doing that Money Ball statistics, they have a solution here.
Now, let's assume that we didn't have those functions, a different way to go is =MOD of this number, ,1 , that gives us just the decimal portion, cuts off the integer and gives us just the decimal.
And let's show a few more decimal places here, alright, so we have our MOD there.
I'm going to use the TEXT function to take that answer, and show it in a fractional 0/3 Custom Number Format, which gets me the fractions of 0/3, 1/3, 2/3.
But we don't need the divided by 3, so I'm going to use the LEFT.
That ,1 , that gets me the portion that should appear after the decimal place, and so we will divide by 10, which gets us the .1 .2 and so on.
And then finally we just have to add back in the original number, the integer of that A9, and copy it down.
This point we have one too many decimals, so we'll go back, Decrease Decimal.
So there's the answer without using DOLLARFR or DOLLARDE.
If you want to go back the other way, same thing, I'll take the MOD, this ,1 which gives us the fraction, the part that goes after the decimal place.
Let's multiply that whole thing times 10, and then divide by 3, which will get us the .1 .2 .3 converted back to a fraction.
And then finally just the integer of the original value, and it converts it back.
Alright, so DOLLARDE and DOLLARFR, two old, old functions left over from the days when stock traders reported eighths, as 10.7 meant 10 and 7/8, works here for this baseball example.
Mike, let's see what you have!
Mike: Thanks MrExcel!
Ah, but we caught you this time!
MrExcel just created this super extra-long, unnecessarily long formula, just to disguise the genius of DOLLARFR, DOLLARDE.
Stock functions we're using for baseball, man, how cool is that!
So you know, I don't really have much choice here, because check this out: That formula right there is the formula I was going to use to go from the .1 .2 to the decimal, however for the decimal to the .1 .2, that long formula MrExcel does.
Basically we're going to do the same formula here, but instead of multiplying by 10 and dividing by 3, we just multiply by 3 and divide by 10.
So that will work.
but I'm never going to use these formulas again, here's how I'm going to do: I'm going to copy MrExcel, I'm just going to do it slightly a different way.
How about I start over here, =DO , down arrow, down arrow, TAB, left arrow, ,3 will be our denominator, CTRL+Enter.
CTRL+C, I'm going to come over here and CTRL+V, now I'm going to use the keyboard shortcut for Find and Replace, CTRL+H. I am going to search for FR, and replace with DE.
Yes, Find and Replace works on formulas, Replace All, click OK, click Esc, and no way!
MrExcel gets 10 points!
Bill: Hey, alright Mike, that was good, I love the Find and Replace over here with CTRL+H.
You know, I told Mike offline that the first time I recorded this, I figured out the DOLLARFR, and I could not figure out the way to go back and.
So, over on the right hand side, I actually used that long formula, I just had this nagging feeling that there was a way to go back.
It was actually when I was watching my first video to edit it, that I noticed here, in this tool tip, the DOLLARDE!
I always thought that was like somehow related to German Dollars or something like that.
Which, of course, is ridiculous because it's Deutschmarks.
But that's surprising "Oh, that's the one I needed right there", was right there in front of me the whole time.
It's funny how sometimes we see things in Excel and don't really focus on them at all, so DOLLARFR and DOLLARDE, I like that.
Well hey, I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and Excel Is Fun!
