Number is whole number

In this example, the goal is to test if a numeric value is a whole number. There are several ways to go about this. One of the easiest ways is to use the MOD function with a divisor of 1. Any whole number divided by 1 will result in a remainder of zero:

=MOD(5,1)=0 // whole numbers return zero

Any decimal number will have a remainder equal to the decimal portion of the number:

=MOD(5.25,1)=0.25

Therefore, we can simply compare the result to zero with a logical expression that returns TRUE or FALSE:

=MOD(5,1)=0 // returns TRUE
=MOD(5.5,1)=0 // returns FALSE

This is the approach taken in the worksheet as shown, where the formula in C5 is:

=MOD(B5,1)=0

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:

=A1=INT(A1)
=A1=TRUNC(A1)

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:

=A1=INT(A1)
=-5.5=INT(-5.5)
=-5.5=-6 // returns FALSE

whereas:

=A1=TRUNC(A1)
=-5.5=TRUNC(-5.5)
=-5.5=-5 // returns FALSE

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.