I have a column of numbers with one decimal point and some blank entries too. I need to convert all the numbers (including the zeros, but not the blanks) to two decimal points. Any idea on how this can be done?
Examples: 0.1 needs to become 0.10, 0 needs to stay 0, a Blank cell needs to remain Blank.
TIA
Add X Decimal Places to Numbers But Not Zeros
-
-
-
Re: Add One Decimal Point And Skip Blanks
Use this Custom Number Format
0.00;-0.00;0
-
Re: Format Numbers For X Decimal Places But Not Zeros
Thanks Dave, but this Custom Format changes how the number is displayed, but not it's value. I need to actually change the value (i.e. from 0.7 to 0.70).
-
-
Re: Add X Decimal Places to Numbers But Not Zeros
Maybe I'm missing something here, but what's the difference between 0.7 and 0.70?
-
-
Re: Add X Decimal Places to Numbers But Not Zeros
My client wants me to report results it in a certain format.
He is expecting 0.70 and not 0.7.
I rather provide the 0.70 than explain it's the same... -
Re: Add X Decimal Places to Numbers But Not Zeros
But surely that's a question of how it is displayed, which is achievable thru custom formatting. Perhaps you could look at the precision as displayed feature under Tools>Options>Calculation tab (note this is a dangerous option to implement as you will permanently lose original data), but I'm not sure that even this will show 0.7 as 0.70 for the reason that Excel sees these two values as being identical.
Richard
-
Re: Add X Decimal Places to Numbers But Not Zeros
QuoteMy client wants me to report results it in a certain format.
Odiseus, you are very confused. That is exactly what the custom number DOES.
QuoteI rather provide the 0.70 than explain it's the same...
I believe anyone over 12 would know there are the same.
Put bluntly, you CANNOT change a value of 0.7 to 0.70. You can ONLY format it to SHOW 0.70
-
Re: Add X Decimal Places to Numbers But Not Zeros
Sadly, I am not confused, since if that was the case, your explanation would have made it clear to me, and the problem would have been solved. Also, I know that mathematically 0.70 is equal to 0.7 (I am over than 12).
So, I want to take a column of numbers, with one decimal point, for example 9.5, 9.8, 0.5 and turn them into 9.51, 9.80 and 0.50, so that all of them would have (and not only show) two digits. If that can't be done in Excel, so be it.
Thanks for all your kind responses.
Odeseus -
Re: Add X Decimal Places to Numbers But Not Zeros
Maybe your not confused, but I am confused by your commentary.
All data in excel is maintained in background as you see it displayed using the “general” format. You also see this general format in the formula bar if the cell contains just a value (not a formula). The data can be displayed in cells to any decimal number desired (within limits). The displayed number will be automatically rounded to the number of decimals specified by the cell format.
If the cell contains a value, only the cell display can be formatted. The formula bar display cannot be formatted. In otherwords, the backgroud data in excel can not be formatted.
Dave’s formula should provide cell display as you requested, unless you are referring to the formula bar display.
-
-
Re: Add X Decimal Places to Numbers But Not Zeros
Odeseus, trust me, not knowing you are confused is compounding your confusion.
QuoteSo, I want to take a column of numbers, with one decimal point, for example 9.5, 9.8, 0.5 and turn them into 9.51, 9.80 and 0.50, so that all of them would have (and not only show) two digits. If that can't be done in Excel, so be it.
For the third time, try the custom format, then wipe off the egg
BTW, I'm lowering your assumed experience.
-
Re: Add X Decimal Places to Numbers But Not Zeros
Hi,
You could maybe try the formula below then Copy | Paste Special | Values to another column. Note - this will result in a mix of text and numbers which could cause problems for later processing.
[COLOR="Blue"]=IF(MOD(A1,1)<>0,TEXT(A1,"#0.00"),A1)[/COLOR]
Eric
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!