This formula returns TRUE if a value is a whole number, and FALSE if not. Any decimal number will have a remainder equal to the decimal portion of the number: Therefore, we can simply compare the result to zero with a logical expression that returns TRUE or FALSE: This is the approach taken in the worksheet as shown, where the formula in C5 is: At each row in the data, the formula returns TRUE for whole numbers only.
INT or TRUNC
Another way to solve the problem is with the INT function or the TRUNC function. In this approach, we run the value through one of these functions and compare the result to the original value. If the values match, we know we have a whole number. The formulas look like this: Both of these formulas compare the original value in A1 to the same value after removing the decimal portion of the number (if any). Both formulas work fine, but note they behave differently with negative decimal values. For example, if A1 contains -5.5: whereas: In short, the TRUNC function actually removes the decimal portion of a number, while the INT function always rounds the number down to the next whole value. This matters for negative values, because they are rounded away from zero (i.e. they become more negative). That said, it doesn’t make a difference in this example. INT still returns the correct result for negative decimal numbers because the integer changes and the result of the comparison is always FALSE.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
