James Elliott - WA Rural Computers wrote:
I think you do not need arrays for this. See the attached to give you an idea.Hi - it's James again with his Calc cashbook and another challenge ...I have used array functions to add up all the debits and credits for differing categories, eg: my code, in Column A, for vehicle expenses is "v" and if I want to know how much I spent on vehicles in the financial year ending 30/6/07, this formula will answer my query:{=SUM((E7:E2000)*(A7:NAB.A2000="v")*(B7:B2000>=D4)*(B7:B2000<=D5))} where D4 is the start date, 1/JULY/06 in this case, and where D5 is the end date, or 30/JUNE/07, and where E is the debits column (expenditure), and where B is the Date column In pseudocode: if(code=i) and if(D4<=Date<=D5) then SUM(Column E)The actual formula I use is complicated by the addition of Sheet-names and generous usage of the $ symbol to make all references Absolute (as distinct from Relative), so the formula does not change when copied from cell to cell, and I think I might have made a typos because it did not work properly when tested.In the end, I selected all the cells and hit Delete, and they all went blank, as one would expect. Now, when I try to enter anything into the cell I get a pop-up error message saying:"You cannot change only part of an array"If I select a cell the array formula is still there, but if I try to delete it, I get:"You cannot change only part of an array" and if I try and edit it (or delete it) in the formula bar, I get: "You cannot change only part of an array"So, I am sorta stuck ... I cannot delete my formulas and cannot proceed until I do.Any advice or suggestion would-be most welcome. James
ELLIOTT.ods
Description: application/vnd.oasis.opendocument.spreadsheet
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
