|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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.
|
|
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" <michaelberrier[ at ]gmail.com> wrote in message
news:1159622113.144341.68260[ at ]m73g2000cwd.googlegroups.com...
[Quoted Text] > 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.
>
|
|
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" <croberts[ at ]tampabay.rr.com> wrote in message
news:O$dDgkJ5GHA.696[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > 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" <michaelberrier[ at ]gmail.com> wrote in message
> news:1159622113.144341.68260[ at ]m73g2000cwd.googlegroups.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.
> >
>
>
|
|
Hi Chuck,
=A1/10^LEN(A1) is just as good, unless I'm missing something.
Ken Johnson
|
|
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" <KenCJohnson[ at ]gmail.com> wrote in message
news:1159627256.643040.183410[ at ]b28g2000cwb.googlegroups.com...
[Quoted Text] > Hi Chuck,
>
> =A1/10^LEN(A1) is just as good, unless I'm missing something.
>
> Ken Johnson
>
|
|
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.
|
|
I was able to get 15 with mine and Ken's..........did you format as I
suggested?
Vaya con Dios,
Chuck, CABGx3
"michaelberrier" <michaelberrier[ at ]gmail.com> wrote in message
news:1159628718.830796.222200[ at ]h48g2000cwc.googlegroups.com...
[Quoted Text] >
> 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.
>
|
|
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!
|
|
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" <michaelberrier[ at ]gmail.com> wrote in message
news:1159629158.599422.89150[ at ]i42g2000cwa.googlegroups.com...
[Quoted Text] >
> 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!
>
|
|
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:
[Quoted Text] >
> 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!
>
>
|
|
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:
[Quoted Text] > 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!
> >
> >
|
|
Thanks for all the help. 100% user-error. I've got it fixed now.
Thanks again.
|
|
|