validate # of decimal
 

I would like to validate cells to limit input to only 2 decimals. If the
user inputs a value with 2 decimals, I want to system to return an error
message.

Is there a way to do this without the use of VB?

validate # of decimal
 

Hi try this solution given some time ago in the community

Copy the code below, right-click the sheet tab, select "View Code" and paste
the code into the window that appears.
I have assumed that you want to do checking only on single cell entries.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If Target.Value < Round(Target.Value, 2) Then
Target.Value = ""
MsgBox "Bad value. Bad, bad, value. Rejected!!!!"
End If
Application.EnableEvents = True
End Sub


"MauraC" wrote:

I would like to validate cells to limit input to only 2 decimals. If the
user inputs a value with 2 decimals, I want to system to return an error
message.

Is there a way to do this without the use of VB?

validate # of decimal
 

MauraC wrote:
I would like to validate cells to limit input to only 2 decimals. If the
user inputs a value with 2 decimals, I want to system to return an error
message.

Is there a way to do this without the use of VB?


Data validation:

=A1*100=(INT(A1*100))

validate # of decimal
 

Data validation:
=NOT(MOD(100*A1,1))

Regards,
Bernd

validate # of decimal
 

Bernd P wrote:
Data validation:
=NOT(MOD(100*A1,1))

Regards,
Bernd


Yes!

Although as an afterthought, I would use this, which makes the number of decimal
places more obvious:


=NOT(MOD(10^2*A1,1))

validate # of decimal
 

"Bernd P" wrote:
Data validation:
=NOT(MOD(100*A1,1))

I believe that has a limit of 1342177.27.

Why not simply:

=TRUNC(A1,2)=A1

validate # of decimal
 

PS....

I wrote:
"Bernd P" wrote:
=NOT(MOD(100*A1,1))

I believe that has a limit of 1342177.27.

Perhaps I need to say "in Excel 2003". I don't know about Excel 2007. Then
again, the OP did not identify the Excel version she is working with.


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

"JoeU2004" wrote in message
...
"Bernd P" wrote:
Data validation:
=NOT(MOD(100*A1,1))

I believe that has a limit of 1342177.27.

Why not simply:

=TRUNC(A1,2)=A1