Data validation with a fixed amount of decimal places
 

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.

Data validation with a fixed amount of decimal places
 

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

Data validation with a fixed amount of decimal places
 

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

Data validation with a fixed amount of decimal places
 

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.

Data validation with a fixed amount of decimal places
 

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.

Data validation with a fixed amount of decimal places
 

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.

Data validation with a fixed amount of decimal places
 

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.

Data validation with a fixed amount of decimal places
 

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

Data validation with a fixed amount of decimal places
 

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

Data validation with a fixed amount of decimal places
 

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

Data validation with a fixed amount of decimal places
 

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

Data validation with a fixed amount of decimal places
 

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.

Data validation with a fixed amount of decimal places
 

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.

Data validation with a fixed amount of decimal places
 

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

Data validation with a fixed amount of decimal places
 

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

Data validation with a fixed amount of decimal places
 

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

Data validation with a fixed amount of decimal places
 

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

Data validation with a fixed amount of decimal places
 

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.