Error using arrays in Calc

[lentzupshaw]

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]

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]

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]

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]

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