OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Trailing zeroes - SOLVED...and then UNSOLVED

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
TwoHands
General User
General User


Joined: 24 Dec 2008
Posts: 10

PostPosted: Wed Dec 24, 2008 7:18 am    Post subject: Trailing zeroes - SOLVED...and then UNSOLVED Reply with quote

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
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Wed Dec 24, 2008 8:40 am    Post subject: Reply with quote

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
View user's profile Send private message
TwoHands
General User
General User


Joined: 24 Dec 2008
Posts: 10

PostPosted: Wed Dec 24, 2008 1:24 pm    Post subject: Reply with quote

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
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu Dec 25, 2008 9:34 am    Post subject: Reply with quote

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. Laughing

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
TwoHands
General User
General User


Joined: 24 Dec 2008
Posts: 10

PostPosted: Thu Dec 25, 2008 7:43 pm    Post subject: Reply with quote

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
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu Dec 25, 2008 10:56 pm    Post subject: Reply with quote

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. Rolling Eyes

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
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Fri Dec 26, 2008 8:08 am    Post subject: Reply with quote

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
View user's profile Send private message
TwoHands
General User
General User


Joined: 24 Dec 2008
Posts: 10

PostPosted: Fri Dec 26, 2008 8:19 am    Post subject: Reply with quote

Bingo. That was it, John. Thanks.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Dec 26, 2008 8:27 am    Post subject: Reply with quote

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
View user's profile Send private message
TwoHands
General User
General User


Joined: 24 Dec 2008
Posts: 10

PostPosted: Tue Dec 30, 2008 8:40 am    Post subject: Reply with quote

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
View user's profile Send private message
thomasjk
Super User
Super User


Joined: 16 Dec 2005
Posts: 2374

PostPosted: Tue Dec 30, 2008 8:46 am    Post subject: Reply with quote

This link may help http://user.services.openoffice.org/en/forum/viewtopic.php?f=7&t=13701 .
Back to top
View user's profile Send private message
TwoHands
General User
General User


Joined: 24 Dec 2008
Posts: 10

PostPosted: Tue Dec 30, 2008 9:40 am    Post subject: Reply with quote

Thanks Thomas. That wasn't it. I save my file in the native OO format.
Back to top
View user's profile Send private message
thomasjk
Super User
Super User


Joined: 16 Dec 2005
Posts: 2374

PostPosted: Tue Dec 30, 2008 10:00 am    Post subject: Reply with quote

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
View user's profile Send private message
TwoHands
General User
General User


Joined: 24 Dec 2008
Posts: 10

PostPosted: Wed Dec 31, 2008 4:30 am    Post subject: Reply with quote

I installed V3.0 this morning but still have the same problem. The search for answers continues Confused ...
Back to top
View user's profile Send private message
hotpepper
Power User
Power User


Joined: 04 Dec 2008
Posts: 52

PostPosted: Thu Jan 01, 2009 3:04 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group