validate # of decimal
 

Would you please help to advise the reason the below formula is not working?

=NOT(MOD(10^4*A1,1))

A1 = 1.11

This is for the validation check

thanks

validate # of decimal
 

How about this?

=NOT(ROUND(MOD(10^4*A1,1),0))

HTH

Bob

"Catherine" wrote in message
...
Would you please help to advise the reason the below formula is not
working?

=NOT(MOD(10^4*A1,1))

A1 = 1.11

This is for the validation check

thanks

validate # of decimal
 

"Bob Phillips" wrote:
How about this?
=NOT(ROUND(MOD(10^4*A1,1),0))

It's called a self-fulfilling prophecy. ;-)

Test with 1.11111, for example.


----- original message -----

"Bob Phillips" wrote in message
...
How about this?

=NOT(ROUND(MOD(10^4*A1,1),0))

HTH

Bob

"Catherine" wrote in message
...
Would you please help to advise the reason the below formula is not
working?

=NOT(MOD(10^4*A1,1))

A1 = 1.11

This is for the validation check

thanks

validate # of decimal
 

"Catherine" wrote:
Would you please help to advise the reason
the below formula is not working?
=NOT(MOD(10^4*A1,1))
A1 = 1.11

Well, for one thing, your formula is trying to validate up to 4 decimal
places, whereas you example has 2 decimal places. Which do you want to
validate?

Try one of the following:

=A1=ROUNDDOWN(A1,2)
=A1=ROUNDDOWN(A1,4)

The first formula validates 2 decimal places. The second validates 4
decimal places.

For a more precise and reliable validation, try one of the following:

=A1-ROUNDDOWN(A1,2)=0
=A1-ROUNDDOWN(A1,4)=0

These are more reliable because they avoid some of Excel's heuristics that
try to "correct" infinitesimal differences, which may or may not be what you
want. See "Caveat" below.

As for why the MOD expression does not work, the reason is: most decimal
fractions cannot be represented exactly internally. Consequently, there are
usually more decimal places than it appears.

For example, the internal representation of 1.11 is actually exactly
1.11000000000000,009769962616701377555727958679199 21875, 10000*A1 is exactly
11100.0000000000,01818989403545856475830078125, and MOD(10000*A1,1) is
exactly 0.00000000000181898940354585,6475830078125. (I use the comma to
demarcate the first 15 significant digits.)

But such anomalies are difficult to predict. For example, 1.1111 is
1.11109999999999,997655208971991669386625289916992 1875, 10000*A1 is exactly
11111, and MOD(10000*A1,1) is exactly 0. In contrast, 1.0011 has a problem
that is similar to 1.11.

The ROUNDDOWN formula works because it asks: is the representation in A1
exactly the same representation to 2 or 4 decimal places.

Caveat: The form A1-ROUNDDOWN(A1,...)=0 is a more precise test because it
(usually[*]) returns FALSE even when the number appears to have only 2 or 4
decimal places when formatted to 15 significant digits, the most precision
that Excel will display. ([*] "Usually" because there are defects in the
Excel ROUNDDOWN and similar functions.)

That may or may not be what you want. For example, compare the results from:

=A1=ROUNDDOWN(A1,4)
=A1-ROUNDDOWN(A1,4)=0

when A1 is =1.1111+22*2^-52. Try formatting A1 as Number with 14 decimal
places. Choose the formula that meets your expectations in this case.


----- original message -----

"Catherine" wrote:
Would you please help to advise the reason the below formula is not working?

=NOT(MOD(10^4*A1,1))

A1 = 1.11

This is for the validation check

thanks

validate # of decimal
 

Thanks very much.
I would use this formula as validation check to limit the user by inputting
the info up to 4 decimal places

=A1-ROUNDDOWN(A1,4)=0



"Joe User" wrote:

"Catherine" wrote:
Would you please help to advise the reason
the below formula is not working?
=NOT(MOD(10^4*A1,1))
A1 = 1.11

Well, for one thing, your formula is trying to validate up to 4 decimal
places, whereas you example has 2 decimal places. Which do you want to
validate?

Try one of the following:

=A1=ROUNDDOWN(A1,2)
=A1=ROUNDDOWN(A1,4)

The first formula validates 2 decimal places. The second validates 4
decimal places.

For a more precise and reliable validation, try one of the following:

=A1-ROUNDDOWN(A1,2)=0
=A1-ROUNDDOWN(A1,4)=0

These are more reliable because they avoid some of Excel's heuristics that
try to "correct" infinitesimal differences, which may or may not be what you
want. See "Caveat" below.

As for why the MOD expression does not work, the reason is: most decimal
fractions cannot be represented exactly internally. Consequently, there are
usually more decimal places than it appears.

For example, the internal representation of 1.11 is actually exactly
1.11000000000000,009769962616701377555727958679199 21875, 10000*A1 is exactly
11100.0000000000,01818989403545856475830078125, and MOD(10000*A1,1) is
exactly 0.00000000000181898940354585,6475830078125. (I use the comma to
demarcate the first 15 significant digits.)

But such anomalies are difficult to predict. For example, 1.1111 is
1.11109999999999,997655208971991669386625289916992 1875, 10000*A1 is exactly
11111, and MOD(10000*A1,1) is exactly 0. In contrast, 1.0011 has a problem
that is similar to 1.11.

The ROUNDDOWN formula works because it asks: is the representation in A1
exactly the same representation to 2 or 4 decimal places.

Caveat: The form A1-ROUNDDOWN(A1,...)=0 is a more precise test because it
(usually[*]) returns FALSE even when the number appears to have only 2 or 4
decimal places when formatted to 15 significant digits, the most precision
that Excel will display. ([*] "Usually" because there are defects in the
Excel ROUNDDOWN and similar functions.)

That may or may not be what you want. For example, compare the results from:

=A1=ROUNDDOWN(A1,4)
=A1-ROUNDDOWN(A1,4)=0

when A1 is =1.1111+22*2^-52. Try formatting A1 as Number with 14 decimal
places. Choose the formula that meets your expectations in this case.


----- original message -----

"Catherine" wrote:
Would you please help to advise the reason the below formula is not working?

=NOT(MOD(10^4*A1,1))

A1 = 1.11

This is for the validation check

thanks

validate # of decimal
 

Hello,

A guess without testing:
=ROUND(A1,4)=ROUND(A1,15)

Regards,
Bernd