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

Error using arrays in Calc

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


Joined: 08 Jan 2007
Posts: 2

PostPosted: Wed Jan 10, 2007 7:31 am    Post subject: Error using arrays in Calc Reply with quote

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?
Back to top
View user's profile Send private message Send e-mail
geoff80fg
OOo Advocate
OOo Advocate


Joined: 26 Jul 2006
Posts: 420
Location: UK

PostPosted: Wed Jan 10, 2007 7:45 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
lentzupshaw
Newbie
Newbie


Joined: 08 Jan 2007
Posts: 2

PostPosted: Wed Jan 10, 2007 8:57 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Wed Jan 10, 2007 10:29 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
geoff80fg
OOo Advocate
OOo Advocate


Joined: 26 Jul 2006
Posts: 420
Location: UK

PostPosted: Thu Jan 11, 2007 12:35 am    Post subject: Reply with quote

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