validate # of decimal

[MauraC]

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?

 

[Eduardo]

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

 

[Glenn]

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

 

[Bernd P]

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

Regards,
Bernd

 

[Glenn]

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

 

[JoeU2004]

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

I believe that has a limit of 1342177.27.

Why not simply:

=TRUNC(A1,2)=A1

 

[JoeU2004]

PS....

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