[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
TwoHands
General User

Joined: 24 Dec 2008
Posts: 10

 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
David
Super User

Joined: 24 Oct 2003
Posts: 5668

 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.
TwoHands
General User

Joined: 24 Dec 2008
Posts: 10

 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.
Sliderule
Super User

Joined: 29 May 2004
Posts: 2499
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.
TwoHands
General User

Joined: 24 Dec 2008
Posts: 10

 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?
Sliderule
Super User

Joined: 29 May 2004
Posts: 2499
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 ) :
1. 2-Decimal where this Style is defined as a numeric: #,##0.00
2. 5-Decimal where this Style is defined as a numeric: #,##0.00000
Next,
1. Select ONE cell, to create your 'template' for conditional formatting. For example . . . cell . . . A2 . . .
2. From the Menu: Format -> Conditional Formatting . . .
3. Put a check in Condition 1
4. In the drop down box, select . . . Formula is
5. 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)

6. In the Cell Style drop down, select your previously created Style: 2-Decmial
7. Put a check in Condition 2
8. In the drop down box, select . . . Formula is
9. 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)

10. In the Cell Style drop down, select your previously created Style: 5-Decmial
11. 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.
1. Copy the contents of cell A2
2. Select all of the OTHER cells, you want to apply similar formatting, for example, a range of A3:A152 and B2:B152 ( or whatever )
3. From the Menu: Edit -> Paste Special...
4. Under Selection . . . the ONLY item with a Check Mark should be: Formats ( uncheck all others )
5. 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.
JohnV

Joined: 07 Mar 2003
Posts: 9183
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###
TwoHands
General User

Joined: 24 Dec 2008
Posts: 10

 Posted: Fri Dec 26, 2008 8:19 am    Post subject: Bingo. That was it, John. Thanks.
Sliderule
Super User

Joined: 29 May 2004
Posts: 2499
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.251111AND a format of #,##0.00### would display:1,234.25 1,234.251 1,234.2511 1,234.25111 1,234.25111I thought . . . TwoHands wanted a display of:1,234.25 1,234.25100 1,234.25110 1,234.25111 1,234.25111If this was my 'misunderstanding' . . . sorry. Sliderule
TwoHands
General User

Joined: 24 Dec 2008
Posts: 10

 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?
thomasjk
Super User

Joined: 16 Dec 2005
Posts: 2374

 Posted: Tue Dec 30, 2008 8:46 am    Post subject: This link may help http://user.services.openoffice.org/en/forum/viewtopic.php?f=7&t=13701 .
TwoHands
General User

Joined: 24 Dec 2008
Posts: 10

 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.
thomasjk
Super User

Joined: 16 Dec 2005
Posts: 2374

 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.
TwoHands
General User

Joined: 24 Dec 2008
Posts: 10

 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 ...
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
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum