Joe Conner wrote:
...
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"
...
Click on any cell that's part of the array area. Type Ctrl+/ This will
expand the selection to include all the cells allocated to the array
formula. Type Backspace. This should remove the array formula.
>> ...
>> {=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)
>> ...
As already pointed out, for a sum you don't have to use an array
formula. The SUM function accepts arrays as arguments and only returns a
single number.
Further, you might want to invest some time to tidy this up a bit: it
would make it easier to understand and maintain.
E.g., you can use the SUMPRODUCT function instead of the explicit
multiplications:
=SUMPRODUCT( E7:E2000 ; A7:NAB.A2000="v" ; B7:B2000>=D4 ; B7:B2000<=D5 )
It works exactly the same as your formula.
Also, if you define names for your columns, as you did in your message,
the formula would look like this:
=SUMPRODUCT( Debits ; Category="v" ; Date>=D4 ; Date<=D5 )
The names will hide all the details about sheet names, absolute/relative
addresses, and so on. Much easier to type as well.
E.g. Select your debits data (E7:E2000), and type "Debits" in the Name
Box at the left of the formula bar. This will define "Debits" as
$SheetN.$A$7:$A:$2000. You can modify that using Insert > Names >
Define, if necessary.
There are some situation where Calc does not accept a name in place of
the reference. Even so, I find that the gain in clarity and resistance
to typos is worth the trouble.
<Joe
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
