Using arrays as function arguments in calc - help! thanks.

[terrorgoat]

I have a microsoft office document with the following ugly array that does not function in Neooffice. Since I despise microsoft office like I despise sudden massive internal hemorrhaging, I would like to find out how to do this in calc.

{=SUM(($CHECKING.$D$2:$D$806="class materials")*
(MONTH($CHECKING.$A$2:$A$806)=MONTH(B$1))*
(YEAR($CHECKING.$A$2:$A$806)=YEAR(B$1))*
$CHECKING.$E$2:$E$806))}

Basically, it appears as though neooffice does not like me using an array as an argument for a function, in this case month or year. I have parsed this thing out and the bit following works:

{=SUM((CHECKING.D2:D806="class materials")*CHECKING.E2:E806)}

but this does not (I get #value!):

{=SUM((MONTH(CHECKING.A2:A806)=MONTH(B1))*
CHECKING.E2:E806)}

Any suggestions? I just want to sum only the dollar array values for a certain year, month and type (here class materials). I think I am just using incorrect syntax but can find no reference to this.

Thanks.

- cjc

[davelentz]

-- and it doesn't need to be a large complex formula. For instance, take this simple example:

A B C

1 2.5 1 =MAX((A1:A5)*(B1:B5))

2 1.2 1

3 3.4 1

4 7.1 0

5 0.1 0

This should produce the value 3.4 in cell C1 -- at least I believe it should -- but it tends to offer 2.5 as the result (which makes me think that only the first element of the product is being passed to MAX(), or that I have not managed to get Neo to recognize that an array formula is involved.

=MAX((A1:A5) correctly produces 7.1

Further, I can't get the curly braces to appear, although pressing F2 when editing the C1 cell highlights the referenced ranges as I expect to see -- entering via SHIFT-CTRL-ENTR is no different than entering via ENTER.

Querying the OO 1.1.4 bugzilla for similar problems turns up a number of issues with array formulas, but they all seem to allude to some basic array formulas working as expected. I can't get the simplest array formula to work according to any of the examples I've seen, although I continue to research this -- it's possible that I'm not doing it right, or that array functions in NeoOffice only worjk when approached a certain way, or that they're just plain busted -- although it would seem that such a thing would have provoked some sort of bug being files by now. I've tested it on several Macs, all running 10.4.2, with both the base and patch-0 version of NeoOffice.

[sardisson]

This is all way beyond my spreadsheet skills but if no one here comes up with an answer, you should check out http://www.oooforum.org, which is filled with a lot more people knowledgeable about the specifics of all the features in OOo (on which Neo/J is based). This shouldn't be a Neo/J- or Mac-specific problem...but that statement and a couple quarters will buy you a Coke

And don't forget the Neo/J Help, either...sometimes OOo does things in an odd way, but usually the Help actually explains the odd way

Smokey

[rays]

[davelentz]

I fumbled around in the ooforums a bit (and there is certainly a lot of confusion on this topic), and then stumbled into actually reading the Neo/J Help -- I know, I may have to turn in my Mac-head decoder ring for actually reading the Help, but it actually *was* helpful.

The first thing I was off in the weeds on was the use of SHIFT-CTRL-ENTER, as with NeoOffice/J (and as it should be with any proper Mac application) the key combo to enter a created/edited array formula reference is SHIFT-COMMAND-ENTER. Serves me right for googling Excel and OO pages for how-to info.

The second thing I got right was by trying to follow verrry carefully the instructions in the Help window. I was eventually able to get my simple example (shown upstream in this topic) to work, but repeating that success has been hit or miss. Apparently it's a very narrow path to follow, and a single misstep is enough to force one to start over. I'm going to keep plugging away at this with my simple example until I get it right and can reliably reproduce the operation.

But it DOES actually work, despite being prey to tiny errors in setting things up.