Always enter number as a decimal <1

[michaelberrier]

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.

 

[CLR]

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

 

[CLR]

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

 

[Ken Johnson]

Hi Chuck,

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

Ken Johnson

 

[CLR]

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

 

[michaelberrier]

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.

 

[CLR]

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

Vaya con Dios,
Chuck, CABGx3

 

[michaelberrier]

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!

 

[CLR]

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

 

[Guest]

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]

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?

 

[michaelberrier]

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

Thanks again.