Limit all entries to 2 decimal places

[fred]

Is there a way in Excel to format a cell so that if someone copy and paste a figure with a long string of decimals, such as 12.345678, that the cell would automatically cut off at 12.34 and become 12.34 or 12.35.


If that can't be done, what is the best way (other than comment) in data validation -> list to tell people who enter a figure that the entry is wrong and should be limited to 2 decimal places? But no warning if the entry is correct?

 

[keymaster]

Try custom data validation. See this image to understand how it works.


Data validation formula


Data validation message

 

[fred]

Thx, Chandoo. Would this matter if I'm using Excel 2007? I don't happen to get the result intended.


I ran into problems


1. if I enter manually say, -1234.56 in the intended cell, which should be fine. However, the error message popped up! And if I copy and paste from a different cell -1234.56. It is fine!


2. when I copy from a cell showing -456.789123 to the intended cell, the whole number would show up. There is no warning to pop me to correct the mistake to enter -456.79 manually.


3. I looked up the MOD function but I'm at a complete lost. What's the usage of it?

 

[Hui]

You can add a quick bit of VBA to the Worksheet Object in VBA to warn you every time a cell changes and it doesn't fit your criteria

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)

For Each c In Target
If InStr(1, c.Text, ".") <> 0 and Len(Target) - InStr(1, Target, ".") > 2 Then
MsgBox "Cell " & Target.Address & " - " & Str(Target.Value) & Chr(10) & "Has too many decimals"
End If
Next

End Sub

[/pre]

 

[Hui]

Fred


For your info


Mod returns the remainder of one number divided into another

eg: mod(50,8) =2

50 divided by 8 = 6 with remainder 2

 

[fred]

Thank you very much, Hui. Now I understand what Mod means.


I have little or no knowledge of VBA and the books I bought on VBA do very little in helping me to learn. The only thing I can understand is creating macro to replace (or speed up) repetitive mouse click. Other than that, it's very hard for me to understand VBA outside a classroom setting.


I think I'll just create a validation warning Chandoo suggested and advise others to be careful in using the worksheet.

 

[Hui]

Fred


Don't be put off by VBA


The above code is self contained and doesn't require any modification to use.


Copy the above code and goto VBA, Alt F11


On the left of the window you will see your workbook and individual sheets

Double click on the worksheet you want the code to apply to


On the right side of the screen there will be a large blank area

Paste the code into there.


Thats it


Go back to your workbook and try entering some numbers


When you save the workbook you will have to save it as a Macro Enabled Workbook

 

[fred]

Hi Hui. Thanks! But it seems to work smoothly only on positive numbers.


I need the cell to be entered as negative figures (discount figure in dollars) by others. The warning would still show up even if I enter a negative figure correctly, say -100.00. And the cell will not reject a negative number with too many decimal places such as -1234.56789 despite the warning.


Is there a way in Excel to stop validating manually entry more than 2 decimal places as in the case of -1234.56789?

 

[Hui]

Fred


It is working ok on negatives for me


Entering -1.234 brings up a message ?

 

[fred]

hi Hui,


after a night of testing I think the problem lies on whole numbers. When I enter -1 (whole number) it will not show the warning message "has too many decimals", which is correct. But if I enter -10 it will show the warning message. So I tested -123 (whole number) and the warning would show up again. So it seems like if the whole figure is -10 or smaller there will be warning.


Then I tested negative figures with one and two decimal, such as -234.5 and -678.23. No warning what so ever. So that is good.