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.

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

"Erin Dicks" wrote in message
...
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.

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.

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.


--
Thanks,
Shane Devenshire


"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.

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))

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

"Erin Dicks" wrote in message
...
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.