Always enter number as a decimal <1
 

I want to be able to enter an integer into a cell and have it
automatically displayed and perceived by excel as a decimal less than
1.

In other words, if I enter "456" into the cell, I want it to display as
".456" all the time. I would further like this formatting to apply to
any number entered in that cell, regardless of how many sig figs there
are, AND I only want it to apply to one cell or a specific range, so
the Tools--Options--Edit--Fixed Decimal Places setting won't help
me.

If possible, I'd like to do this without a macro so it can be used with
Pocket Excel.

Thanks to all.

Always enter number as a decimal <1
 

You've taken away all the easy options <g. The only other thing I can
think of is to use a helper cell to type the number in and put this formula
in the cell where you want the desired display..........

=A1/10^LEN(REPT(0,LEN(A1)))-INT(A1/10^LEN(REPT(0,LEN(A1))))

Then to omit the leading zero from the display that Excel normally puts on
decimal numbers you would have to format it Custom as
..################

hth
Vaya con Dios,
Chuck, CABGx3


"michaelberrier" wrote in message
ups.com...
I want to be able to enter an integer into a cell and have it
automatically displayed and perceived by excel as a decimal less than
1.

In other words, if I enter "456" into the cell, I want it to display as
".456" all the time. I would further like this formatting to apply to
any number entered in that cell, regardless of how many sig figs there
are, AND I only want it to apply to one cell or a specific range, so
the Tools--Options--Edit--Fixed Decimal Places setting won't help
me.

If possible, I'd like to do this without a macro so it can be used with
Pocket Excel.

Thanks to all.

Always enter number as a decimal <1
 

Sorry,........sent the wrong formula.........here's the actual finished
one.........

=A1/10^LEN(REPT(0,LEN(A1)))

also must be formatted Custom as .################

hth
Vaya con Dios,
Chuck, CABGx3


"CLR" wrote in message
...
You've taken away all the easy options <g. The only other thing I can
think of is to use a helper cell to type the number in and put this
formula
in the cell where you want the desired display..........

=A1/10^LEN(REPT(0,LEN(A1)))-INT(A1/10^LEN(REPT(0,LEN(A1))))

Then to omit the leading zero from the display that Excel normally puts on
decimal numbers you would have to format it Custom as
.################

hth
Vaya con Dios,
Chuck, CABGx3


"michaelberrier" wrote in message
ups.com...
I want to be able to enter an integer into a cell and have it
automatically displayed and perceived by excel as a decimal less than
1.

In other words, if I enter "456" into the cell, I want it to display as
".456" all the time. I would further like this formatting to apply to
any number entered in that cell, regardless of how many sig figs there
are, AND I only want it to apply to one cell or a specific range, so
the Tools--Options--Edit--Fixed Decimal Places setting won't help
me.

If possible, I'd like to do this without a macro so it can be used with
Pocket Excel.

Thanks to all.

Always enter number as a decimal <1
 

Hi Chuck,

=A1/10^LEN(A1) is just as good, unless I'm missing something.

Ken Johnson

Always enter number as a decimal <1
 

Hi Ken..........

Yup, I kinda-sorta backed into my formula........but the good news is, both
work.
Thanks for the enlightenment tho.

Vaya con Dios,
Chuck, CABGx3



"Ken Johnson" wrote in message
oups.com...
Hi Chuck,

=A1/10^LEN(A1) is just as good, unless I'm missing something.

Ken Johnson

Always enter number as a decimal <1
 

Chuck/Ken;

Thanks for the help, guys. However, either of those formulas limit me
to 3 sig figs after the zero, and I would like to have more. What can
I adjust in the simpler formula:
=A1/10^LEN(A1)

To allow for, say, 5? Is that possible?

Thanks again.

Always enter number as a decimal <1
 

I was able to get 15 with mine and Ken's..........did you format as I
suggested?

Vaya con Dios,
Chuck, CABGx3



"michaelberrier" wrote in message
oups.com...

Chuck/Ken;

Thanks for the help, guys. However, either of those formulas limit me
to 3 sig figs after the zero, and I would like to have more. What can
I adjust in the simpler formula:
=A1/10^LEN(A1)

To allow for, say, 5? Is that possible?

Thanks again.

Always enter number as a decimal <1
 

Having said that, I just realized that this is a simple math formula
that needs to be divided by a different factor of ten based on how many
digits I want to use...duh.

So...I guess there isn't a solution that will allow the number of
digits to be variable?

Thanks again!

Always enter number as a decimal <1
 

The combination of the formula and the formatting takes care of
that.........
Either formula will display 1 or up to 15 digits after the decimal point.

Vaya con Dios,
Chuck, CABGx3


"michaelberrier" wrote in message
ups.com...

Having said that, I just realized that this is a simple math formula
that needs to be divided by a different factor of ten based on how many
digits I want to use...duh.

So...I guess there isn't a solution that will allow the number of
digits to be variable?

Thanks again!

Always enter number as a decimal <1
 

The solution provided allows for a variable number of digits

12==0.12
1234=0.1234
1234567=0.1234567

You can leave cell formatted as general but make wide enough to take largets
number

"michaelberrier" wrote:


Having said that, I just realized that this is a simple math formula
that needs to be divided by a different factor of ten based on how many
digits I want to use...duh.

So...I guess there isn't a solution that will allow the number of
digits to be variable?

Thanks again!

Always enter number as a decimal <1
 

I must be typing something wrong in the formula then (I cut and pasted,
though) because if I enter "12", I get .012, if I enter "12345" I get
123.45.

I copied the formulas exactly as you put them in your post(s), so where
do you think I'm going wrong?

Toppers wrote:
The solution provided allows for a variable number of digits

12==0.12
1234=0.1234
1234567=0.1234567

You can leave cell formatted as general but make wide enough to take largets
number

"michaelberrier" wrote:


Having said that, I just realized that this is a simple math formula
that needs to be divided by a different factor of ten based on how many
digits I want to use...duh.

So...I guess there isn't a solution that will allow the number of
digits to be variable?

Thanks again!

Always enter number as a decimal <1
 

Thanks for all the help. 100% user-error. I've got it fixed now.

Thanks again.