Work with formulas and formats: Decimals and fractions

- [Narrator] Most Excel users at one time or another need to deal with formats involving numbers and decimals. Working with fractions is less common. Let's check out both of these using different techniques. If you're a baseball fan, you know that batting average is calculated by taking the number of hits it doesn't make any difference whether they're home runs or doubles or whatever, the number of hits divided by times at bat. So imagine this batter right here. What's his average equal the number of hits divided by times at bat.

But in baseball terminology, we'd call this 318 and we certainly don't want it displayed like that. So first order of business might be on the Home tab, let's use the Comma button and let's display three decimals so to the right we can increase the number of decimals but there's still that leading zero out there that we wouldn't want. There's no standard method that we see here that's going to get rid of that zero but what we could do is go into Format Cells, by way of Control + 1, that's one way, and on the Number tab, go to the category called Custom and the Custom format that we want here is .000.

Click OK and that's the way we would want to see it. And in any kind of sports calculation where you don't want a leading zero when the numbers are below zero, that's the format you want to use. Now displayed to the right, I'll put it in with the single quotes so it stays there looking the way it looks, in reality .000, that's the format being used for this kind of a calculation. Now, when you type fractions, and I'm about to type right here 5/16, exactly the way you see it up there, look what happens.

I wasn't thinking of 16th of May but that's the fifth month, 16th day. How about another fraction that you want to use? How about 530 seconds? There aren't 32 days in any month, what's going to happen now? Now Excel turns this into May 1st of 1932. So what's going on here? Well let's try it this way. =5/16. Now that stores a decimal and that is the correct value. That is the value of 5/16ths. That certainly is what we want in some cases, but how to we actually get the 5/16 to display? We type a leading zero.

So here we'll type zero space 5/16. And then we see it does display that way and furthermore, as you look in the formula bar or if you happen to double click, .3125 is what we want to see. The question is how often will you need these? Before 2000, the stock market started using decimal quotes, they used fractions and perhaps, then you would have been more likely to have used them. Now there are times when you might want to display numbers as a fraction. Over in cells B10, 11 and 12, I've got the same number here.

But I'm going to right click and go into Format Cells. Remember I could press Control + 1, and this time in the category under Number, let's choose fraction. There are three variations here on how you can display these numbers as fractions either with one digit denominator or two digit or three digit. Let's start with the one digit and see what that looks like. Well, that's close but that would be 5.4 and that's not the real number. It's 5.4124. So, we can display it this way.

The real value is still being stored there. If we use this in calculations, we will be using 5.4124 but perhaps it's misleading to see the number like this. Up to two digits here too, we can right click and go into Format Cells. This time we'll use Fraction and display this up to two digits. And there we have a bigger number. Now that's probably closer to being accurate than this one but again, the display here is not really the issue in one sense. It's the same number.

It's 5.4124. Again, you can see it up on the formula bar. Let's do it one more time, this time with three digits. Right click Format Cells, Fraction, Up to three digits. Click OK and there we see a pretty unwielding number. Now again, it's questionable as to how you might want to use these but, keep in mind, that these are not really the values that are being stored. In fact, if I were to go over here, for example, right now and type =5 2/5 I think you know what I'm going to be getting here it's going to be 5.4 and that's the pure value but here if I type =5 40/97 I think you know it's going to be a little bit different.

We can display those with even more decimals but I think you get the point here too and sure enough, on the last example, =5 153/371. And notice the difference here. Remember the real value in all these cases is and we see it in the formula bar there, 5.4124 even though these different fractional displays emerged over here. So you want to be careful with using these fractions. Any time you use the fractional displays, run the risk of displaying information that isn't truly accurate even though the accurate values are behind the scenes.

By the way a little bit of historical trivia up here, if you divide these numbers in the opposite direction, in other words, equal that 355 divided by 113 you will get a number that's very, very close to pi but not quite. Someone figured that out centuries ago. Anyway, I think you get the idea here. As we work with decimals and/or fractions at different times we have to make adjustments, we have to enter the data differently than we might think but we can work with them numerically, always being careful to recognize that displayed fractions aren't always the real values that we're working with.