Thanks Brian I do appreciate what you are saying I am using an array formuls as I am testing openoffice in this area Regards Ken
-----Original Message----- From: Brian Barker [mailto:[email protected]] Sent: 11 December 2008 08:58 To: [email protected] Subject: Re: [users] copying a single cell array formula by dragging it down 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] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
