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.

Maybe something like:

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


wrote:

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


wrote:

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

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.

"
wrote in message
...
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.

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.

"
wrote in message
...
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.

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

"Leo Rod" wrote:

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.

"
wrote in message
...
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.

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

"Leo Rod" wrote:

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.

"
wrote in message
...
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 wrote...
....
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############")

Dave Peterson wrote...
....
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############")

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.
--
David Biddulph

"Harlan Grove" wrote in message
ups.com...
Dave Peterson wrote...
...
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".

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.
--
David Biddulph

"Harlan Grove" wrote in message
ups.com...
Dave Peterson wrote...
...
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".

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.


"
wrote in message ...
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

"Leo Rod" wrote:

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.

"
wrote in message
...
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.

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.


"
wrote in message ...
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

"Leo Rod" wrote:

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.

"
wrote in message
...
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.

"David Biddulph" <groups [at] biddulph.org.uk wrote...
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.
....
"Harlan Grove" wrote in message
Dave Peterson wrote...
...
or
=--TEXT(A1,"0.000")=A1
....
Guaranteed not to work when users enter, e.g., 1.500, 3.250, etc.,
....

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.

"David Biddulph" <groups [at] biddulph.org.uk wrote...
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.
....
"Harlan Grove" wrote in message
Dave Peterson wrote...
...
or
=--TEXT(A1,"0.000")=A1
....
Guaranteed not to work when users enter, e.g., 1.500, 3.250, etc.,
....

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.

I agree that =round() is more clear.

(as I wipe the sweat from my forehead.)

Harlan Grove wrote:
=ROUND(A1,3)=A1

would be clearer.

--

Dave Peterson

I agree that =round() is more clear.

(as I wipe the sweat from my forehead.)

Harlan Grove wrote:
=ROUND(A1,3)=A1

would be clearer.

--

Dave Peterson

Thank you to everyone for your help.

" wrote:

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.