How do i get only whole numbers for a formula result?

[Guest]

I am working with one of our engineers that wants to have a cell with a
formula show only whole numbers. He does not want rounding, but to be able
to code a statement that will error out if the result is a deciaml or
fraction. I looked at the Data - validation properties, but it only works
with an entered number not a formula. Is there a simple way to do it without
having to write a program in vb?

 

[STEVE BELL]

For the formula try:

=If(Mod(A1/B1,1)>0, "Error",A1/B1)

Or you can use Mod in a conditional format and change the cell format if in
error.

 

[JE McGimpsey]

One way:

Say the formula is

=B1/A1

Then you could use

=IF(MOD(B1/A1,1)=0, B1/A1,"Error")

However, due to XL's internal rounding, it's possible to have small
remainders (e.g., 15.0000000000001).

For that reason it's usually best to compare the result of the formula
to a small value that is larger than the largest expected error, but
significantly smaller than the expected result. For instance:

=IF(MOD(B1/A1,1)<0.000000000001, ROUND(B1/A1,0), "Error")

See http://cpearson.com/excel/rounding.htm for more on rounding errors

 

[Tom Ogilvy]

=if(trunc(formula)<>formula,"Error",formula)