increment cell reference in a formula

[MarkW1307]

Hi All,
I am using a DAVERAGE formula and need to increment the criteria reference
when I use the fill command.
my formula is =DAVERAGE('Rhizome Counts I'!$B$3:$I$6,"RhizKG2",B4:B5)
I want to be able to Fill Down this formula and increment B4:B5 by 2 each
time i.e. B6:B7 B8:B9 B10:B11 etc i Have tried the Serires option on the fill
function but this didnt work. Any suggestions.
Thanks

 

[Pete_UK]

Instead of B4:B5, put this:

INDIRECT("B"&(2*ROW(A1)+2)&":B"&(2*ROW(A1)+3))

so that your formula becomes:

=DAVERAGE('Rhizome Counts I'!$B$3:$I$6,"RhizKG2",INDIRECT("B"&(2*ROW
(A1)+2)&":B"&(2*ROW(A1)+3)))

Hope this helps.

Pete

 

[MarkW1307]

This seems to work.
Thanks very much!

Instead of B4:B5, put this:

INDIRECT("B"&(2*ROW(A1)+2)&":B"&(2*ROW(A1)+3))

so that your formula becomes:

=DAVERAGE('Rhizome Counts I'!$B$3:$I$6,"RhizKG2",INDIRECT("B"&(2*ROW
(A1)+2)&":B"&(2*ROW(A1)+3)))

Hope this helps.

Pete

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete