how do you retrict data entry to max of 2 decimal places

[ashleydiane1432]

How do you restrict a user to only be able to enter up to 2 decimal places in a cell without it automatically rounding for them?

[playaller]

Highlight all columns or cells used > right click > format cells > Select Number under (Number Tab)> Change Decimal Places to the number 2. Hit OK

[Tony Valko]

You can use data validation to do this.

Let's assume you want to limit cell A1 to numbers with no more than 2 decimal places.

Select cell A1
Goto the menu Data>Validation
Allow: Custom
Formula: =TRUNC(A1,2)=A1
OK out

You can have custom input and error messages if desired.

[ashleydiane1432]

playaller that does not give me the results I want, that will let them enter more than 2 decimal places and will automatically round to only show 2 decimals.

Tony Valko I knew there was probably a custom formula for the DV but I didn't know what it was. The formula you gave me is coming back with an error. Can you double check it?

[Tony Valko]

Tony Valko I knew there was probably a custom formula for the DV but I didn't know what it was. The formula you gave me is coming back with an error. Can you double check it?

What kind of error?

Are you testing it as a formula entered in a cell? If so, depending on what you try to enter in the cell it may return an error but when used as a DV formula that error has the same effect as not permitting the cell entry.

[ashleydiane1432]

I figured it out! Thank you Tony!

[Tony Valko]

Good deal. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.