Over the years, I’ve been asked how to conditionally use decimal places with numbers. In Part 1 of this series, I used a custom number format to accomplish this. Although the technique is simple, it also limits the tests you can apply.
Part 2 of this series uses the MOD function with conditional formatting to display decimal places only when a number includes a fractional amount. This time I’ll explain how to use the TRUNC function in Excel to conditionally format decimal values for percentages.
The TRUNC function removes any fractional parts of a number without rounding. Conversely the INT function also removes fractional parts of a number, but in addition rounds down the smaller number.
You won’t notice this with numbers greater than zero, TRUNC and INT will both return the same value. However, TRUNC and INT will return different values for numbers that are less than zero, as shown in Figure 1.
TRUNC and INT only work with greater than 1 or less than -1, so the formulas in Figure 1 multiply and divide by 100 to allow the function to perform the calculation, and then restore the amounts back to decimal values.
About David Ringstrom, CPA
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.