[Solved] Number format - accounting

Discuss the spreadsheet application

[Solved] Number format - accounting

Postby egwkansascity » Sun Nov 16, 2008 5:44 pm

I am embarressed to say I have been unable to find the comparible function in calc to excel that allows me to simply select a number format for accounting work that provides for () around negative numbers and the ability to designate no decimel places and apply this format to the whole worksheet as the number format for the sheet. I would prefer that every new sheet open with this as the default format.

Sorry to interject such a simple concept need - maybe I have simply missed it - truly new to calc but wanting to use it for all of my spreadsheet needs.

Thanks - Eldon
Last edited by Hagar Delest on Sun Nov 16, 2008 10:38 pm, edited 1 time in total.
Reason: tagged [Solved].
OOo 2.3.X on Ms Windows XP
egwkansascity
 
Posts: 2
Joined: Sun Nov 16, 2008 5:30 pm

Re: number format - accounting

Postby bobban » Sun Nov 16, 2008 6:07 pm

Hi Eldon,

A 'true' accounting format is not possible I believe, because it is not possible to have numbers on the right and a currency symbol on the left in one cell. I am not experienced with accounting but I have seen a number of people inquire about this . The most commonly proposed workaround used to have a very narrow column alongside containing just the currency symbol. When you are looking at the preset formats, you can manually edit the format code at the bottom. If you press the help button, there is a good explanation of all the various options, and it is not as hard as it seems to create unique formats, especially when you can base them on the existing precepts.In regards to your situation, here is a format code based on the "positive and negative numbers" section of the help manual:

(#,###.00);[RED]-#,###.00;#,###.00

The first section is related to positive numbers, and hence contains the brackets, the second section is related to negative numbers, and you can remove the "red" if you want, and the last section is for numbers equal to zero.

To make this format a permanent feature of your Calc, you must edit the default template.

If this question is entered, please edit the original post thread title by putting [Solved] in it.
OOo 3.1.1 on Ms Windows XP
User avatar
bobban
Volunteer
 
Posts: 535
Joined: Sat Nov 01, 2008 3:12 pm
Location: Australia

Re: number format - accounting

Postby egwkansascity » Sun Nov 16, 2008 7:24 pm

Bobban - thanks for the help.

The format string you gave me did not get me a negative number in () but - using this concept - here is what I did to solve my problem and then created the default temp file to use going forward.

1) I opened an existing excel file with negative () numbers in it - I opened it with oorg and found that the set up string for this format then existed in the number format in oorg file.
2) I copied the string and opened a new oorg worksheet and then in "cells-format-numbers-user defined", I pasted the format string that I copied from excel.
3) That gave me a choice then in the new worksheet to selected that format for the worksheet and I made it global to the sheet.
4) I then went through your instructions for saving the worksheet as a new template and then I also went through the steps of making it the default worksheet when I opened a new worksheet.
5) It seems to work. WOW!!

Here is the formula I lifted from the from the oorg/excel file and pasted in oorg template for "cell-numbers-user defined".
[>0]#,##0 ;[<0]" ("#,##0);" - ";@

Thanks for the help - problem solved for now.

Eldon
OOo 2.3.X on Ms Windows XP
egwkansascity
 
Posts: 2
Joined: Sun Nov 16, 2008 5:30 pm

Re: [Solved] Number format - accounting

Postby bobban » Mon Nov 17, 2008 4:46 am

The format string you gave me did not get me a negative number in () but - using this concept - here is what I did to solve my problem and then created the default temp file to use going forward.


You're right, I hurried my response and misread that the brackets should be around negative numbers, not positive. I should have suggested:

#,###;(#,###);0

or

#,###;(#,###);" - " , if you want " - " instead of zero.

Note: You're solution is very similar in fact. It just uses 'conditional' operators (>0, <0 etc), but when there are just three terms separated semi colons Calc interprets these as relating to positive, negative, zero.

Good working getting it sorted.
OOo 3.1.1 on Ms Windows XP
User avatar
bobban
Volunteer
 
Posts: 535
Joined: Sat Nov 01, 2008 3:12 pm
Location: Australia

Re: [Solved] Number format - accounting

Postby chewybb » Sat Feb 01, 2014 10:22 am

I'm new to spreadsheets, but did some BASIC programming many, many years ago.

Here's what I came up with...I know it's a bit long winded, but it seems to work well from initial tests. I've never used Excel so I can't compare the results. Please let me know if it works for you.

=CONCATENATE("£";REPT(" ";10-LEN(TEXT(F3;"#####0.##")));TEXT(F3;"##0.##"))

:D
OpenOffice 3.4.1 on Windows 7
chewybb
 
Posts: 4
Joined: Sun Jun 30, 2013 6:18 pm

Re: [Solved] Number format - accounting

Postby chewybb » Sat Feb 01, 2014 10:25 am

I forgot to add, this needs to be used with a monospace font such as Courier New, unfortunately.
OpenOffice 3.4.1 on Windows 7
chewybb
 
Posts: 4
Joined: Sun Jun 30, 2013 6:18 pm

Re: [Solved] Number format - accounting

Postby Alex1 » Sat Feb 01, 2014 11:20 am

This means you need an extra cell for each value. You can replace the leading #'s by spaces in an existing format to achieve the same.
AOO 4.0.1, LO 4.1.5 on Windows XP, 7
Alex1
 
Posts: 160
Joined: Fri Feb 26, 2010 1:00 pm
Location: Netherlands


Return to Calc

Who is online

Users browsing this forum: No registered users and 18 guests