Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Very Peculiar DV error Q
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Very Peculiar DV error Q
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Very Peculiar DV error Q
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Very Peculiar DV error Q
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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Very Peculiar DV error Q
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Very Peculiar DV error Q
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Very Peculiar DV error Q
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Very Peculiar DV error Q
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Very Peculiar DV error Q
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) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Very Peculiar DV error Q
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.11999999999999921840299066388979554176330566406 25, 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 "Sean" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Peculiar Date problem | New Users to Excel | |||
Peculiar date format behavior when replacing parts of date | Excel Discussion (Misc queries) | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
How to do a peculiar sort? | New Users to Excel | |||
A peculiar PDF from Excel problem | Excel Discussion (Misc queries) |