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

Author Message
lentzupshaw
Newbie

Joined: 08 Jan 2007
Posts: 2

 Posted: Wed Jan 10, 2007 7:31 am    Post subject: Error using arrays in Calc I am using Calc for budget worksheets and have 3 columns for credit, debit and balance. I use arrays to calculate the balance. I used this format in Excel and it worked fine, but in Calc it does not calculate the balance correctly. Originally I opened an excel file in Calc, but then I tried creating a new sheet in Calc but had the same problem. Any suggestions?
geoff80fg

Joined: 26 Jul 2006
Posts: 420
Location: UK

 Posted: Wed Jan 10, 2007 7:45 am    Post subject: I think you need to post a typical formula that you are using. I presume that you are aware of the different separators used within Calc when compared with Excel. Examples are always better understood than general descriptions. Geoff
lentzupshaw
Newbie

Joined: 08 Jan 2007
Posts: 2

 Posted: Wed Jan 10, 2007 8:57 am    Post subject: Thanks Geoff, No, am not aware of the different separators used between Excel and Calc. If you could help me out with that I would appreciate it. Also, the formula is as follows Credit Debit Balance 0 0 0 2 0 2 0 1 1 To calculate the balance in row 2, I take the balance in row 1, add the credit in tow 2, and subtract the debit in row 2. I use arrays for this in the following way. The array for the result is the balance column from row 2 to row 301. To get this result I start with an array in the balance column from row 1 to row 300, add the credit array from row 2 to row 301 and subtract the debit array from row 2 to row 301. I hope that helps. Lentz
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

 Posted: Wed Jan 10, 2007 10:29 am    Post subject: Can you copy some example formulae and say which cells they're in, and whether they are really array formulae (with the { and } round them). Otherwise it's little more than guesswork for me (and probably other people) as to how you have actually implemented what you describe.
geoff80fg

Joined: 26 Jul 2006
Posts: 420
Location: UK

 Posted: Thu Jan 11, 2007 12:35 am    Post subject: I believe that you are confusing yourself with the term "array". An array is a group of cells which may all be in one row, all in one column or across several rows and columns. I do not believe that you actually need arrays for your example since you are keeping a running total in the balance column as you go along. For a typical budget sheet you might start with a "Brought Forward" row where there would be no Credit or Debit entry but a figure in the Balance column. Let's say we put Date in A, Details in B, Credit in C, Debit in D and Balance in E. Headings would be in row 3 (say, to allow room for Overall Heading) Brought Forward goes in row 4 with the value in E4. The next row holds a formula in E5 which is: =IF(A5="";"";E5=E4+C5-D5) What this does is, if a date is entered in the first column, take the previous balance, add any Credit value and subtract any Debit value. This formula can be copied from cell E5 and pasted down as many cells in column E as required and produces a running total in the final row that contains values. I hope this is what you want and that you understand what is happening - and all without using arrays. Geoff
 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