how to check if a number has no more than 2 decimal digits

  • Thread starter Thread starter zxcv
  • Start date Start date
Z

zxcv

I need to do some input validation so to check if a value has no more
than 2 decimal digits. So 14.12 is valid but 14.123 is not.

I have tried doing a check like this:

If Int(inval * 100) <> inval * 100 Then

but this gets a rounding error with certain values like 2.22. If I
subtract one side above from the other I get a difference on the order
of 10^-21.

So I tried rounding the numbers to do a test like this:

If Round(Int(inval * 100), 10) <> Round(inval * 100, 10) Then

and this does something weird like rounding Round(Int(0.29 * 100), 10)
to 28.

Is there some simpler way to check that a number does not have too
many decimal digits?
 
This worked pretty good. You can adapt it to your needs.

Sub dk()

For Each c In Range("A2:A6")
If Len(c) - InStr(c, ".") > 2 Then
MsgBox c.Address & " More than 2 decimal places"
End If
Next
End Sub
 
Your code will fail if the number is a whole number without a decimal point.
If you change your If..Then statement to the following, then your code will
work correctly...

If Len(c) - InStr(c & ".", ".") > 2 Then
 
zxcv said:
I need to do some input validation so to check if a
value has no more than 2 decimal digits. So 14.12
is valid but 14.123 is not.
Click to expand...

Try:

If Round(inval,2) = inval Then

Normally, I would opt for WorksheetFunction.Round or even
Evaluate("round(...)") instead of the VB Round function. There are
functional differences. In this case, I do not think it makes a difference.
Nevertheless, you might want to use one of those alternatives instead, just
to be sure.


----- original message -----
 
By fail I meant it will return the wrong result, not error out. I assumed
from the OP's posting that whole numbers as well as floating point numbers
with one or two decimal places were okay... your original If...Then
statement reported one and two digits after the decimal point as being okay,
but listed whole numbers as having more than two decimal places (if the
whole number had more than two digits in it).
 
Your code will fail if the number is a whole number without a decimal point.
If you change your If..Then statement to the following, then your code will
work correctly...

If Len(c) - InStr(c & ".", ".") > 2 Then
Click to expand...

Thanks. A combination of the 2 above approaches is working.
 
Try:

If Round(inval,2) = inval Then

Normally, I would opt for WorksheetFunction.Round or even
Evaluate("round(...)") instead of the VB Round function.  There are
functional differences.  In this case, I do not think it makes a difference.
Nevertheless, you might want to use one of those alternatives instead, just
to be sure.

----- original message -----
Click to expand...

Thanks for the input but I need to do this in VBA as I have no control
over the input and cannot put any formulas in the sheet. Someone else
enters the data and then another person hits a button that I created.
 
Embellishment....

zxcv said:
I tried rounding the numbers to do a test like this:
If Int(inval * 100) <> inval * 100 Then

but this gets a rounding error with certain values like 2.22.
Click to expand...

The reason that does not work is because most numbers with decimal fractions
cannot be represented exactly. Instead, they are represented by a sum of 53
consecutive powers of two (bits), for example 2*2^1 + 0*2^0 + 0*2^-1 +
0*2^-2 + 1*2^-3 + etc.

Consequently, 2.22 is represented by exactly
2.22000000000000,0195399252334027551114559173583984375. Int(2.22*100) is
exactly 222. But 2.22*100 is
222.000000000000,028421709430404007434844970703125, preserving the
additional bits used to approximate 0.22 in this context.

In contrast, Round(inval,2) results in inval exactly as it would be
represented internally if it were entered with 2 decimal places. So if
inval is 2.22, Round(inval,2) results in
2.22000000000000,0195399252334027551114559173583984375. But if inval were
2.22+2^-51 (the smallest value larger than 2.22), it would be represented
internally as 2.22000000000000,06394884621840901672840118408203125, and
Round(inval,2) does not equal inval.

Note: You cannot enter the
2.2200000000000006394884621840901672840118408203125 as a constant in Excel;
however, it can be the result of a calculation. Also, you can enter that
constant in VBA, including as input to an InputBox. Caveat: If you write
that constant in a VBA statement, the VBA editor might change it later when
you edit the line. It would be more reliable to write
Cdbl("2.2200000000000006394884621840901672840118408203125").


----- original message -----
 
Try:
If Round(inval,2) = inval Then
Click to expand...
[....]
Thanks for the input but I need to do this in VBA
as I have no control over the input and cannot put
any formulas in the sheet. Someone else enters the
data and then another person hits a button that I
created.
Click to expand...

I don't understand your comment. What I wrote is for VBA, and it is
intended to deal with exactly the situation that you describe. I think you
misunderstand my comments. Perhaps you should just give it a try.

PS: Sorry, I wrote "=" where you wanted "<>". That's a simple change, heh?


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

Try:

If Round(inval,2) = inval Then

Normally, I would opt for WorksheetFunction.Round or even
Evaluate("round(...)") instead of the VB Round function. There are
functional differences. In this case, I do not think it makes a
difference.
Nevertheless, you might want to use one of those alternatives instead,
just
to be sure.

----- original message -----
Click to expand...

Thanks for the input but I need to do this in VBA as I have no control
over the input and cannot put any formulas in the sheet. Someone else
enters the data and then another person hits a button that I created.
 
Yes, I did not account for the 3 digit whole number. The modified code
below would also eliminate that possibility and restrict the items tested to
only those with decimal values.

Sub decDig()
For Each c In Range("A2:A5")
If InStr(c, ".") > 0 Then
If Len(c) - InStr(c, ".") > 2 Then
MsgBox c.Address & " OK"
End If
End If
Next
End Sub
 
Try:
If Round(inval,2) = inval Then
Click to expand...
[....]
Thanks for the input but I need to do this in VBA
as I have no control over the input and cannot put
any formulas in the sheet.  Someone else enters the
data and then another person hits a button that I
created.
Click to expand...

I don't understand your comment.  What I wrote is for VBA, and it is
intended to deal with exactly the situation that you describe.  I thinkyou
misunderstand my comments.  Perhaps you should just give it a try.

PS:  Sorry, I wrote "=" where you wanted "<>".  That's a simple change, heh?

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


If Round(inval,2) = inval Then
Click to expand...
Normally, I would opt for WorksheetFunction.Round or even
Evaluate("round(...)") instead of the VB Round function. There are
functional differences. In this case, I do not think it makes a
difference.
Nevertheless, you might want to use one of those alternatives instead,
just
to be sure.
Click to expand...
----- original message -----
Click to expand...

Thanks for the input but I need to do this in VBA as I have no control
over the input and cannot put any formulas in the sheet.  Someone else
enters the data and then another person hits a button that I created.
Click to expand...

I see! I did not know that the WorksheetFunction object existed. I
will make use of this more in the future.

Thank you.
 
Embellishment....




The reason that does not work is because most numbers with decimal fractions
cannot be represented exactly.  Instead, they are represented by a sum of 53
consecutive powers of two (bits), for example 2*2^1 + 0*2^0 + 0*2^-1 +
0*2^-2 + 1*2^-3 + etc.

Consequently, 2.22 is represented by exactly
2.22000000000000,0195399252334027551114559173583984375.  Int(2.22*100) is
exactly 222.  But 2.22*100 is
222.000000000000,028421709430404007434844970703125, preserving the
additional bits used to approximate 0.22 in this context.

In contrast, Round(inval,2) results in inval exactly as it would be
represented internally if it were entered with 2 decimal places.  So if
inval is 2.22, Round(inval,2) results in
2.22000000000000,0195399252334027551114559173583984375.  But if inval were
2.22+2^-51 (the smallest value larger than 2.22), it would be represented
internally as 2.22000000000000,06394884621840901672840118408203125, and
Round(inval,2) does not equal inval.

Note:  You cannot enter the
2.2200000000000006394884621840901672840118408203125 as a constant in Excel;
however, it can be the result of a calculation.  Also, you can enter that
constant in VBA, including as input to an InputBox.  Caveat:  If you write
that constant in a VBA statement, the VBA editor might change it later when
you edit the line.  It would be more reliable to write
Cdbl("2.2200000000000006394884621840901672840118408203125").

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

"Joe User" <joeu2004> wrote in message
Click to expand...

Thanks. That is a great bit of information. I never knew why the
rounding errors happened.
 
Back
Top