If you need to confirm a number is a whole number you can use a function with a short name.
The INT function (it is short for INTEGER) which will remove any decimals from a number. INTEGER is a mathematical and programming term for a whole number.
If cell A1 has a number and you want to validate it as a whole number you could use.
=A1=INT(A1)
This will display TRUE if the number is a whole number and FALSE if it isn’t.
If you aren’t sure the cell will hold a number and it might hold text or even an error you can use.
=IF(ISNUMBER(A1),A1=INT(A1))
This will display FALSE for text and avoid the #VALUE! error.
Note: The IF function doesn’t have a FALSE argument and so it will simply display FALSE if the ISNUMBER function returns FALSE.
This formula also handles errors in cell A1.
Some examples shown below.
Very well explained, Neale.
This provides a false positive for negative numbers which are not whole numbers. The function does not work.
Hi Danny
What number gave the false positive? I tested some negatives and they worked ok.
Regards
Neale
Is there a way to prevent a number which isn’t an integer from being entered if your field requires integers. eg I have a field that I require to only have integers in that is to be completed by a third party and they must ensure hat the answer is an integer.
The following Quantity = 300 with a Carton Quantity = 30would give me an Answer= 10. This is acceptable but if the 300 were changed to 400 I don’t want the third party to be able to enter a quantity that won’t give you an integer as the answer. Ths cell must then remain blank
Hi Peter
Yes it possible i will send you an email of the solution.
Regards
Neale