increment cell reference in a formula
 

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

increment cell reference in a formula
 

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

On Jan 9, 9:09*am, MarkW1307
wrote:
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

increment cell reference in a formula
 

This seems to work.
Thanks very much!

"Pete_UK" wrote:

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

On Jan 9, 9:09 am, MarkW1307
wrote:
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

increment cell reference in a formula
 

You're welcome - thanks for feeding back.

Pete

On Jan 9, 11:16*am, MarkW1307
wrote:
This seems to work.
Thanks very much!