| View previous topic :: View next topic |
| Author |
Message |
TwoHands
General User
Joined: 24 Dec 2008
Posts: 7
|
 Posted: Wed Dec 24, 2008 7:18 am Post subject: Trailing zeroes - SOLVED...and then UNSOLVED |
 |
|
I'm using Calc to keep track of stock purchases. Some of them go to 5 decimal places but most are the standard $#.##. Is there a way of displaying the extra decimals when appropriate without showing other amounts as {for example} $20.00000?
Last edited by TwoHands on Tue Dec 30, 2008 8:41 am; edited 2 times in total |
|
| Back to top |
|
 |
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Wed Dec 24, 2008 8:40 am Post subject: |
|
|
If I read you right, cell number formatting works for me. You can determine the number of decimal places to be show. Change to 5 decimal places, then it doesn't matter if you enter 20, 20.00, or 20.00000.
David. |
|
| Back to top |
|
|
TwoHands
General User
Joined: 24 Dec 2008
Posts: 7
|
| Posted: Wed Dec 24, 2008 1:24 pm Post subject: |
|
|
Thanks David but not quite what I'm looking for. The cell formatting is set like so...
#,###.#####
...so I can have detail when the purchase price gets to five decimals. But with this format any trailing zero gets cut off. If I make a purchase for 21.50 it displays as...
21.5
What I'd like to do is display a minimum of two decimals and as many as five when appropriate. I hope that makes it clearer. |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2477
Location: 3rd Rock From The Sun
|
| Posted: Thu Dec 25, 2008 9:34 am Post subject: |
|
|
TwoHands:
According to Calc Help: format codes;numbers
| Calc Help: format codes;numbers wrote: | Decimal Places and Significant Digits
Use zero (0) or the number sign (#) as placeholders in your number format code to represent numbers. The (#) only displays significant digits, while the (0) displays zeroes if there are fewer digits in the number than in the number format.
Use question marks (?) to represent the number of digits to include in the numerator and the denominator of a fraction. Fractions that do not fit the pattern that you define are displayed as floating point numbers.
If a number contains more digits to the right of the decimal delimiter than there are placeholders in the format, the number is rounded accordingly. If a number contains more digits to the left of the decimal delimiter than there are placeholders in the format, the entire number is displayed. Use the following list as a guide for using placeholders when you create a number format code:
| Code: |
Placeholders Explanation
------------ -----------
# Does not display extra zeros.
0 (Zero) Displays extra zeros if the number has less places than zeros
in the format. |
| Code: |
Examples
Number Format Format Code
------ ------ ------ ----
3456.78 as 3456.8 ####.#
9.9 as 9.900 #.000
13 as 13.0 and 1234.567 as 1234.57 #.0#
5.75 as 5 3/4 and 6.3 as 6 3/10 # ???/???
.5 as 0.5 0.##
|
|
Therefore, perhaps you should format your code as: #,###.00000 when you want 5 decimal places to be displayed ( the trailing zerso in the code above will ensure 5 decimals are displayed ).
Additional note . . . I would recommend you construct a Style(s) for the numeric code(s) you need. That way, it will be easier to apply / maintain the codes.
I hope this helps, please be sure to let me / us know. 
Sliderule
Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved. |
|
| Back to top |
|
|
TwoHands
General User
Joined: 24 Dec 2008
Posts: 7
|
| Posted: Thu Dec 25, 2008 7:43 pm Post subject: |
|
|
Sliderule, thanks for helping out. This still doesn't do what I want -- the #,###.00000 format will display all prices with five digits. I'm likely being too picky here and since no one sees this except my accountant and myself it's probably not worth all the effort.
I'm not a programmer but ideally, I'd set up the field for five digits after the decimal point and not to display the rightmost three digits if they equal "000". Is there code that will allow me to do that? |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2477
Location: 3rd Rock From The Sun
|
| Posted: Thu Dec 25, 2008 10:56 pm Post subject: |
|
|
TwoHands:
You said: | TwoHands wrote: | | I'm not a programmer but ideally, I'd set up the field for five digits after the decimal point and not to display the rightmost three digits if they equal "000". Is there code that will allow me to do that? |
OK, I think I now better understand what you want.
Can it be done . . . yes . . . but . . . it will take alittle bit of 'effort' on your part. One way I have thought of . . . is by using . . . Conditional Formatting. But, this will involve alittle bit of 'ground-work' on your part to implement it.
In English . . . you will have to set up TWO format styles . . . for example ( you can choose other names, if you desire ) :- 2-Decimal where this Style is defined as a numeric: #,##0.00
- 5-Decimal where this Style is defined as a numeric: #,##0.00000
Next, - Select ONE cell, to create your 'template' for conditional formatting. For example . . . cell . . . A2 . . .
- From the Menu: Format -> Conditional Formatting . . .
- Put a check in Condition 1
- In the drop down box, select . . . Formula is
- Enter EITHER formula from below . . . you can decide ( change A2 to the cell you are using ) :
- IF(A2 * 100 - FLOOR(A2 * 100;1) = 0; 1; 0)
- IF(RIGHT(TEXT(A2;"#.00000");3) = "000"; 1; 0)
- In the Cell Style drop down, select your previously created Style: 2-Decmial
- Put a check in Condition 2
- In the drop down box, select . . . Formula is
- Enter EITHER formula from below . . . you can decide ( change A2 to the cell you are using ) :
- IF(A2 * 100 - FLOOR(A2 * 100;1) <> 0; 1; 0)
- IF(RIGHT(TEXT(A2;"#.00000");3) <> "000"; 1; 0)
- In the Cell Style drop down, select your previously created Style: 5-Decmial
- Press the OK button
Now, you have ONE instance of Conditional Formatting . . . with 2-Decimal and 5-Decimal 'correctly' applied. The next job is to use . .. Paste Special . . . . to apply this newly created "Conditional Formatting" to all of your other cells.- Copy the contents of cell A2
- Select all of the OTHER cells, you want to apply similar formatting, for example, a range of A3:A152 and B2:B152 ( or whatever )
- From the Menu: Edit -> Paste Special...
- Under Selection . . . the ONLY item with a Check Mark should be: Formats ( uncheck all others )
- Press OK button
I hope that makes sense, try it . . . and . . . check the Calc Help file for:- conditional formatting;conditions
- conditional formatting;cells
- formats;assigning by formulas
I know this may seem 'complicated' . . . but . . . I suspect . . . once you try it . . . you will become an 'expert' real quick. 
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved. |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8995
Location: Lexinton, Kentucky, USA
|
| Posted: Fri Dec 26, 2008 8:08 am Post subject: |
|
|
I understand the OP to want at least two decimal place but up to 5 if there is data to be displayed.
#,##0.00### |
|
| Back to top |
|
|
TwoHands
General User
Joined: 24 Dec 2008
Posts: 7
|
| Posted: Fri Dec 26, 2008 8:19 am Post subject: |
|
|
| Bingo. That was it, John. Thanks. |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2477
Location: 3rd Rock From The Sun
|
| Posted: Fri Dec 26, 2008 8:27 am Post subject: |
|
|
Just as an FYI . . . I thought ( yes, Sliderule thinking . . . that is dangerous ) . . . that TwoHands wanted either, two decimal places, OR, five decimal places displayed.
BUT, a number input or calculated like:- 1234.25
- 1234.251
- 1234.2511
- 1234.25111
- 1234.251111
AND a format of #,##0.00### would display:- 1,234.25
- 1,234.251
- 1,234.2511
- 1,234.25111
- 1,234.25111
I thought . . . TwoHands wanted a display of:- 1,234.25
- 1,234.25100
- 1,234.25110
- 1,234.25111
- 1,234.25111
If this was my 'misunderstanding' . . . sorry.
Sliderule |
|
| Back to top |
|
|
TwoHands
General User
Joined: 24 Dec 2008
Posts: 7
|
| Posted: Tue Dec 30, 2008 8:40 am Post subject: |
|
|
Sliderule, even tho you got this one wrong I hope you'll keep thinking. And now, something else to think about...
The new formatting, #,##0.00###, isn't saved when I close the program and reopen - it reverts to #.###.#####.
Is this a known bug in OO 2.3 {I know and I really will update one of these days} or am I doing something wrong? |
|
| Back to top |
|
|
thomasjk
Super User
Joined: 16 Dec 2005
Posts: 2310
|
|
| Back to top |
|
|
TwoHands
General User
Joined: 24 Dec 2008
Posts: 7
|
| Posted: Tue Dec 30, 2008 9:40 am Post subject: |
|
|
| Thanks Thomas. That wasn't it. I save my file in the native OO format. |
|
| Back to top |
|
|
thomasjk
Super User
Joined: 16 Dec 2005
Posts: 2310
|
| Posted: Tue Dec 30, 2008 10:00 am Post subject: |
|
|
| Perhaps its time to try V3.0. You can install V3.0 along side V2.x and run both. Just don't import the user preferences, its a bit buggy. |
|
| Back to top |
|
|
TwoHands
General User
Joined: 24 Dec 2008
Posts: 7
|
| Posted: Wed Dec 31, 2008 4:30 am Post subject: |
|
|
I installed V3.0 this morning but still have the same problem. The search for answers continues  ... |
|
| Back to top |
|
|
hotpepper
Power User
Joined: 04 Dec 2008
Posts: 52
|
| Posted: Thu Jan 01, 2009 3:04 pm Post subject: |
|
|
Make the default number formatting for the cell a number with 2 decimal places.
Make 3 styles for 3Decimal, 4Decimal, and 5Decimal with the appropriate number of decimal places for each.
I'm using cell C1 in this example:
Select cell C1:
In Conditional Formatting, use Formula Is for each of the Conditions
Check Condition 1, change Cell Value Is to Formula Is and put in the following formula:
AND(TRUNC(C1;3)=C1;TRUNC(C1;2)<>C1)
Cell Style: 3Decimal
Check Condition 2, change Cell Value Is to Formula Is and put in the following formula:
AND(TRUNC(C1;4)=C1;TRUNC(C1;2)<>C1)
Cell Style: 4Decimal
Check Condition 3, change Cell Value Is to Formula Is and put in the following formula:
AND(TRUNC(C1;5)=C1;TRUNC(C1;2)<>C1)
Cell Style: 5Decimal
Click OK |
|
| Back to top |
|
|
|
