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

[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?

 

[JLGWhiz]

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

 

[Rick Rothstein]

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

 

[Joe User]

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.

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

 

[JLGWhiz]

Hi Rick, I ran a test and it did not error out. But it does not hurt to be
safe.

 

[Rick Rothstein]

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).

 

[zxcv]

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

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

 

[zxcv]

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

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.

 

[Joe User]

Embellishment....

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.

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]

Try:
If Round(inval,2) = inval Then

[....]
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.

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

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.

 

[JLGWhiz]

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

 

[zxcv]

Try:
If Round(inval,2) = inval Then

[....]
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.

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

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

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.

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

Thank you.

 

[zxcv]

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

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