At 15:11 10/12/2008 +0000, Ken Jolliffe wrote:
I have written a simple single cell array formula but when I drag it
down to copy to the cells below the relative reference does not
change. This behaviour is different to Excel but I am sure that I
simply need to know the Openoffice way of doing things.
The formula is as follows
{=sum(if($A$1:$A$10=A12;$B$1:$B$10;0))}
When I drag it down the reference A12 does not change to A13,A14...etc.
As has already been explained, the solution is not to drag the
formula but to copy it. Just copy the cell with the original array
formula, select the entire range you want filled, and paste. If,
after you do this, you select one cell of the range and press Ctrl+/,
you will see that the selection will not expand: you have created
separate array formulae rather than extending the singe cell into an array.
But there's an easier solution, since you don't, I think, need an
array formula at all. If I understand what you are doing correctly,
this will work:
=SUMIF(A$1:A$10;A12;B$1:B$10)
(If you quote just a cell for the criterion - as here: A12 - the
default comparison is "equals".)
Since this isn't an array formula (although some of its parameters
are arrays), you can drag it to fill the range you require.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
