Repeating Excel Formula every N rows

1
votes

I am trying to add a repeating formula in Excel that will sum a range of values in another column (scores) for each subject to get a Total Score for each subject.

I have added the following formula which seems to total the values correctly:

SUM(OFFSET($AO$2,(ROW()-424)*424,0,424,1))

You can see it here
]: https://i.stack.imgur.com/8DIjK.png

Here is the result I currently have from adapting the formula...

Image 2

Does anyone know how can I get this formula to repeat every 425 rows in this column (AQ)?

Thanks for any tips.

2
excelexcel-formula
use a Union of the cells and apply the formula to the union. – user4039065
@Jeeped even I don't know what you mean with that. – teylyn
@teylyn - set rng = union(cells(425, "AQ"), cells(850, "AQ"), cells(1275, "AQ")) then rng.formula = "SUM(OFFSET($AO$2,(ROW()-424)*424,0,424,1))" The union could be aggregated in a For ... Next loop. – user4039065
Ah, but only if the formula is applied using VBA. – teylyn
@teylyn - ... but I wouldn't use the OFFSET function and would probably just run a formula that checks the MOD of the row all the ways down the column (leaving a zero-length string if not multiple of 425). – user4039065

2 Answers

2
votes

You could use this formula in cell AQ2 and copy down:

=IF(MOD(ROW()-1,425),"",SUM(OFFSET($AO$2,(ROW()-424)*424,0,424,1)))

It will perform the calculation only in every 425th row, starting in row 426. You can use a 0 instead of the "" and format the range not to show zeros.

Edit: Your Offset formula is not correctly calculating the range of the 424 cells preceding the current row. It is a bit too complicated. You can start in the current row and use a negative number in the row offset, like this:

=IF(MOD(ROW()-1,425),"",SUM(OFFSET(AO2,-424,0,424,1)))

So, from the current row, go 424 rows up, then use a range that has 424 rows. That will sum the 424 rows above the current row.

Edit 2: Here is a screenshot with the formula working on column BE instead of AQ. Rows between the pertinent rows have been hidden.

enter image description here

The sample file can be accessed here:

https://1drv.ms/x/s!Avd3VBkllUf-7zeMXBcqxfyc9-d-

0
votes

In ,

Dim i As Long, rng As Range
With Worksheets("sheet3")
    Set rng = .Cells(426, "AQ")
    For i = 426 To .Cells(.Rows.Count, "AO").End(xlUp).Row Step 425
        Set rng = Union(rng, .Cells(i, "AQ"))
    Next i
    rng.FormulaR1C1 = "=sum(r[-424]c41:r[-1]c41)"
End With