| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro - How to increment cell reference by one row | Excel Worksheet Functions | |||
| Need formula to increment cell reference every 8th row | Excel Discussion (Misc queries) | |||
| Increment cell reference | Excel Discussion (Misc queries) | |||
| how to make cell address reference increment? | Excel Worksheet Functions | |||
| Row reference increment but preserve column reference | Excel Worksheet Functions | |||
Linear Mode
