Join Dennis Taylor for an in-depth discussion in this video Use INT, TRUNC, ODD, and EVEN for specialized rounding, part of Excel: Advanced Formulas and Functions.
- [Instructor] When you work with certain kinds of data and formulas in Excel, sometimes you don't care about the decimal portion of it. You simply want to throw it away. There are two functions and many times they're very similar, in fact they're almost identical with one major exception. One's called TRUNC, T R U N C. Think of the word truncate. The other one's called INT. Think of the word integer. We got some calculations in column C here. We don't need to see the formulas necessarily. Here are the results. We don't really want these results here. We don't need the pennies. So we've got a formula here that actually could be in column C called TRUNC. And what is TRUNC doing here? It's simply dropping the decimals. It's not rounding up or down, it's just throwing them away. And INT over here is doing the same kind of thing. Now ultimately, this could have been over in column C. We could have used either one. So right here TRUNC. Left parenthesis is good enough, enter. There it is. And we wouldn't need to display the decimals. It simply threw them away. And we get the same answer here if we used INT. Same idea, same result. So either one is going to work using the formula this way. Here we did it separately just to point out the difference in how they would look. But there is a difference if you're dealing with negative numbers. And the descriptions in D2 and E2 describe the difference. TRUNC always drops the decimal portion. So this is a negative number here. If we use TRUNC here, just throw away those decimals like we said earlier. But with Integer, that might be surprising to you how we think of Integer. We're not just saving the integer portion here, we're going farther away from zero. In other words, we're moving away from zero. It returns a lower value here. And that's consistent with the description. INT always gives us a lower value. Compare these values with these. Obviously the ones over here are lower. The same thing happens over here. I tend to use the INT function most of the time. But whenever negatives are involved, I stop and think about this. And sometimes I have to come back and remind myself the difference in the two. But we see how they work. With positive numbers, it's a wash, makes no difference. But with negatives, yes, they're different. Two other functions not so widely used but available. Sometimes you want to take a result and simply convert it to, it's not really rounding, but given the nearest odd number. The nearest odd number to 22. Now you might say, well, isn't 21 the same? The odd number, nearest odd number, moving away from zero. 21 is moving closer to zero. So this is moving away from zero. We see how that's working. And of course as soon as you know this you suspect, and there it is even doing the same kind of thing. Of course if it's the same, if it's already an even number, you do this. What if this is 22.01? Moves to 24. It's almost 22 but we move away from zero to the next even number. You see it's happening there. So specialized uses to be sure for even and odd. But more commonly perhaps one of these TRUNC, or INT, as we saw earlier in samples can be used. Four more functions related to rounding in Excel.
- Displaying and highlighting formulas
- Debugging formulas
- Creating 3D formulas
- Creating nested IF functions
- Exploring VLOOKUP, MATCH, and INDEX
- Tabulating data with COUNTIFS, SUMIFS, and AVERAGEIFS
- Finding values with statistical functions
- Adjusting results with rounding functions
- Converting values between measuring systems
- Calculating dates
- Returning reference data
- Manipulating text
- Extracting information