Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Always enter number as a decimal <1
Hi Chuck,
=A1/10^LEN(A1) is just as good, unless I'm missing something. Ken Johnson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Always enter number as a decimal <1
Thanks for all the help. 100% user-error. I've got it fixed now. Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot a change a number 10.0 to 10 , cannot take out the decimal | Excel Discussion (Misc queries) | |||
" / " Changing Decimal number format to Fraction on Protected Cell | Excel Worksheet Functions | |||
How do I insert 2 decimal places to a number in cell? | Excel Worksheet Functions | |||
number formats default to 2 decimal points when entered so 24 bec. | Excel Discussion (Misc queries) | |||
How do I enter a prefix number to a colum of numbers | Excel Worksheet Functions |