I have the DV below that checks to only allow any value between 0.01
and 40000.00 in cell H10 provided a value exists first in J10.

The most peculiar thing is happening. If I type say 10 (a whole
number), it accepts - which is correct. If I then delete this value
and re-enter a 2 decimal value, it rejects it (but it shouldn't as its
valid)

Why is this happening

=AND(J10<"",H10=0.01,H10<=40000,MOD(100*H10,100)-
INT(MOD(100*H10,100))=0)

What's the problem using this DV formula:

=AND(J10<"",H10=0.01,H10<=40000)

With "Ignore Blank"
*Unchecked*
?

--

Regards,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Sean" wrote in message
ups.com...
I have the DV below that checks to only allow any value between 0.01
and 40000.00 in cell H10 provided a value exists first in J10.

The most peculiar thing is happening. If I type say 10 (a whole
number), it accepts - which is correct. If I then delete this value
and re-enter a 2 decimal value, it rejects it (but it shouldn't as its
valid)

Why is this happening

=AND(J10<"",H10=0.01,H10<=40000,MOD(100*H10,100)-
INT(MOD(100*H10,100))=0)

Thanks RD, only problem is I don't want anymore than 2 decimal places
(I use the data to import and 3 decimals is bad). The user can input
10, I don't nescessarily want them to input as 10.00

Works fine for me.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sean" wrote in message
ups.com...
I have the DV below that checks to only allow any value between 0.01
and 40000.00 in cell H10 provided a value exists first in J10.

The most peculiar thing is happening. If I type say 10 (a whole
number), it accepts - which is correct. If I then delete this value
and re-enter a 2 decimal value, it rejects it (but it shouldn't as its
valid)

Why is this happening

=AND(J10<"",H10=0.01,H10<=40000,MOD(100*H10,100)-
INT(MOD(100*H10,100))=0)

Bob it works for me too.. sometimes, not sure if its because of
refresh calculations, but file is only 540kb I've even hit F9 before I
input. Would this DV formula be termed 'volatile', although not sure
what exactly this means

An update on this, if you put this formula in A14

=AND(J14<"",H14=0.01,H14<=40000,MOD(100*H14,100)-
INT(MOD(100*H14,100))=0)

And in H14 type 10.12 and J14 type 123

The answer will return FALSE - why would that be?

Rounding errors. In general you can't express decimal numbers exactly in
fixed point binary. 0.5 or 0.25 or 0.125 can be expressed exactly, but 0.1
or 0.12 cannot. [Just as 1/3 cannot be expressed exactly in fixed point
decimal.]

You assumed that =MOD(100*H14,100) gave 12, but if you format with enough
decimal places it shows 11.9999999999999. You've then subtracted the INT
result 11 from it and tested for the result being equal to 0, and it isn't,
hence the answwer FALSE. You may wish to allow an appropriate tolerance in
your tests, or incorporate some rounding.

As a hint for the future, if you don't understand the result of a formula,
break it down into manageable chunks and look at each part in turn.
--
David Biddulph

"Sean" wrote in message
oups.com...
An update on this, if you put this formula in A14

=AND(J14<"",H14=0.01,H14<=40000,MOD(100*H14,100)-
INT(MOD(100*H14,100))=0)

And in H14 type 10.12 and J14 type 123

The answer will return FALSE - why would that be?

Thanks David, is it possible to return the value to the right of the
decimal point eg. 10.12 would be 12; 10.123 would be 123?

I'm trying to test if a user has input more than 2 decimal places, and
if so disallow it, so hence any value up to 99 would be acceptable.
That's what I was trying to do with the original formula