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?

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.
--
rand451
"mkirkley" wrote in message
...
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?

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

In article ,
"mkirkley" wrote:

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?

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

--
Regards,
Tom Ogilvy


"mkirkley" wrote in message
...
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?