Copying non-contiguous formulas
B1: =SUM(INDIRECT("A"&(ROW()-1)*25+1&":A"&ROW()*25))
copy down as needed
This will sum A1:A25 in B1, A26:A50 in B2, etc....
If you were starting in B2, it would be
=SUM(INDIRECT("A"&(ROW()-2)*25+2&":A"&(ROW()-1)*25+1))
Then, for each lower row, subtract 1 more from each row() argument, and add
1 more at the end, so row 3 would be ROW()-3*25+3 and (ROW()-2)*25+2
etc.
Hope this helps.
--
** John C **
"Erin Dicks" wrote:
I want to copy down formulas refering to ranges of 25 rows (e.g. sum rows
1-25, sum rows 26-50 etc.) but when I copy the formula down, it only adds one
to each reference, not 25 (even when I've supplied several correct formulas).
Are there any tricks for getting around this? I have to copy the formula down
100 times and don't really want to type each one.
|