Data validation with a fixed amount of decimal places

[Guest]

I would like to force a user to enter a number that contains a decimal and
three spaces beyond (0.000). I think this can be done with the Data
Validation feature but I am having difficulty writing the formula. The
number of places to the left of the decimal can be limitless and the number
to the right could be less than 3, but I need to have the 'missing' places
populated with zeros.

 

[Dave Peterson]

Maybe something like:

=A1=INT((A1*1000))/1000
or
=--TEXT(A1,"0.000")=A1

 

[Leo Rod]

Use cell properties with a custom number that ends in 3 spaces, filling when
shorter or cutting when longer.
In the cell, set of cell you want to change left click select cell
properties, custom and write under type : "#,##0.000" whitout quotes, then
click OK.

This will do.

"(e-mail address removed)"

 

[Guest]

I was hoping to use Data Validation so I could post a reminder to the user
when entering the data in the field. Would you have any suggestions to
accomplish this?

Thank you

 

[Harlan Grove]

or
=--TEXT(A1,"0.000")=A1

....

Guaranteed not to work when users enter, e.g., 1.500, 3.250, etc., in
which case Excel will dutifully evaluate A1 as text as "1.5" or "3.25"
for these two sample entries, and those won't equal "1.500" or
"3.250".

The only way to ensure users enter 3 and only 3 decimal places every
time involves giving the entry cell the number format Text, so Excel
would store EXACTLY what the user entered.

However, it looks to me like the OP just needs to format the entry
cell with 3 decimal places. If s/he also needs to use these entries as
though they include 3 decimal places, then all references to the entry
cell need to be wrapped in TEXT calls, e.g., to refer to X99,

=TEXT(X99,"0.000")

or

=TEXT(X99,"0.000############")

 

[David Biddulph]

In which version of Excel is this "guaranteed not to work", Harlan?
It works in Excel 2003, and I see no reason why it shouldn't work.

 

[Leo Rod]

Add an input message to cell validation when "any value" or if you want
restrict it to numerical values, this together with the custom cell format.

 

[Harlan Grove]

In which version of Excel is this "guaranteed not to work", Harlan?
It works in Excel 2003, and I see no reason why it shouldn't work. ....

....

Yup, I screwed up. Dave's formula binds as

(--TEXT(A1,"0.000"))=A1

That being the case,

=ROUND(A1,3)=A1

would be clearer.

 

[Dave Peterson]

I agree that =round() is more clear.

(as I wipe the sweat from my forehead.)

 

[Guest]

Thank you to everyone for your help.