validate # of decimal

  • Thread starter Thread starter Catherine
  • Start date Start date
C

Catherine

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
 
Bob Phillips said:
How about this?
=NOT(ROUND(MOD(10^4*A1,1),0))
Click to expand...

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

Test with 1.11111, for example.


----- original message -----
 
Catherine said:
Would you please help to advise the reason
the below formula is not working?
=NOT(MOD(10^4*A1,1))
A1 = 1.11
Click to expand...

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,00976996261670137755572795867919921875, 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,9976552089719916693866252899169921875, 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 -----
 
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 said:
Catherine said:
Would you please help to advise the reason
the below formula is not working?
=NOT(MOD(10^4*A1,1))
A1 = 1.11
Click to expand...

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,00976996261670137755572795867919921875, 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,9976552089719916693866252899169921875, 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 said:
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
Click to expand...
Click to expand...
 
Back
Top