Exceljet

Quick, clean, and to the point

Number is whole number

Excel formula: Number is whole number
Generic formula 
=MOD(A1)=0
Summary 

To test if a number is a whole number, you can use the MOD function. In the example shown, the formula in C5, copied down, is:

=MOD(B5,1)=0

This formula returns TRUE if a value is a whole number, and FALSE if not.

Explanation 

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 formula 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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.