Joe
I have been tidying up my spreadsheet as you suggested, using SUMPRODUCT,
and also defining names for my columns, and it looks good and works well.
One annoying thing I find when editing formulas is this ...
in the formula: =SUMPRODUCT( Debits ; Category="p" ; Date>=D4 ; Date<=D5 )
if I want to change the category from "p" to "v", when I type the "v" it
autocorrects to "Vlookup()" and I get:
=SUMPRODUCT( Debits ; Category="Vlookup()" ; Date>=D4 ; Date<=D5 )
Similarly "i" autocorrects to "IF()" and so on for many of the letters I
use.
I know it should be a simple matter to turn this function off, but I have
looked and turned off what seems to be appropriate, but it is still doing
it, and I would be very much obliged if you (or anyone else on this list)
could point me in the right direction to turn this feature off.
Many thanks,
James
----- Original Message -----
From: "Joe Smith" <[EMAIL PROTECTED]>
To: <users@openoffice.org>
Sent: Wednesday, January 09, 2008 6:05 PM
Subject: [users] Re: ARRAY FUNCTIONS.?
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]
--
No virus found in this incoming message.
Checked by AVG Free Edition. Version: 7.5.516 / Virus Database:
269.17.13/1212 - Release Date: 6/01/2008 10:55 PM
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
