Very Peculiar DV error Q

[Sean]

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)

 

[RagDyeR]

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!
=====================================================


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)

 

[Sean]

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

 

[Bob Phillips]

Works fine for me.

--
---
HTH

Bob

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

 

[Sean]

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

 

[Sean]

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?

 

[David Biddulph]

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.

 

[Sean]

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

 

[Sean]

Just an update on this, I think I have a formula that does what I
require, it tests for

a) There is a value in J10
b) H10 as entered is <=0.01 and >=40000.00
c) The user only inputs a max of 2 decimals (I test that the length of
H10 - to the right of the decimal, is no longer than 2 - if user only
enters a whole number it returns 0, thus a length of 1 i.e. still
valid as an entry)



=AND(J10<>"",H10>=0.01,H10<=40000,LEN((IF(ISERROR(RIGHT(H10,LEN(H10)-
FIND(".",H10))),0,RIGHT(H10,LEN(H10)-FIND(".",H10)))))<=2)

 

[Jerry W. Lewis]

If you are checking manually entered values (not calculated values), then
compare the entered value with ROUND(value,2).

With calculated values, you would have to allow that there might be
differences beyond the 15th decimal figure that should be ignored. In that
case, it might be easier to convert the number to a string and substring it
at the decimal point (becomes slightly more complicated if scientific
notation might be involved).

As has been pointed out, most decimal factions have no exact binary
representation. For instance the binary approximation to 10.12 is
10.1199999999999992184029906638897955417633056640625, so =10.12-10 will
correctly return 0.119999999999999. With a different integer part, the
approximation to 0.12 could change, so numerically obtaining the factional
part is of limited value for your purpose. With calculated values there are
56 distinct binary values that to 15 figures (Excel's documented display
limit) display as 10.1200000000000.

Jerry