Copying non-contiguous formulas

[Erin Dicks]

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.

 

[Harald Staff]

Hi

Try
=SUM(INDIRECT("A"&INT((ROW()-1)*25)+1&":A"&INT(ROW()*25)))
Note: must start in row 1 as is.

HTH. Best wishes Harald

 

[John C]

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.

 

[ShaneDevenshire]

Hi,

1. Assuming I summing A1:A25 in B25. You can enter the formula on say B25,
then highlight B25 down to B49 and then drag the fill handle. The formula
will repeat every 25 or so lines and refer to the appropriate range. But
they will be 25 rows apart. If you want them all together, select from row
25 down to the last formula. Press Ctrl+G, Special, Blanks. Press Ctrl+-
(control minus) and shift cells up.

2. You can use a formula approach with INDIRECT.

 

[MyVeryOwnSelf]

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.

Here's one way.

If the data is in column A, put this in B1 and copy down:
=SUM(OFFSET($A$1,25*(ROW()-1),0,25,1))

 

[Peo Sjoblom]

One way

=SUM(INDEX($A$1:$A$10000,ROWS($A$1:A1)*25-24):INDEX($A$1:$A$1000,ROWS($A$1:A1)*25))

replace only this range $A$1:$A$10000 if
your data starts somewhere else



--


Regards,


Peo Sjoblom