Filling Down formulas

[Bagman]

In cell N9 I am averaging a set of data from another tab in the workbook:
=AVERAGE('Daily Actual Data'!F21:F27)

In cell N10 I am averaging another set of data from another tab in the workbook:
=AVERAGE('Daily Actual Data'!F28:F34)

In cell N11:N100 I would like the formula to continue to fill down adding 7 to each side of the cell group. Is there an easy way to make this formula fill down in this way without having to enter each group individually?

 

[id107]

Edit: Nope my answer didn't work. Oops.

 

[Bagman]

Tried this. Excel repeats the set of 2 groups. It does not see the +7 pattern

 

[id107]

Quick macro solution

Sub test()
    Dim c As Range, RowNum As Integer
    RowNum = 21
    For Each c In Range("N11:N100")
        c.Formula = "=AVERAGE('Daily Actual Data'!F" & RowNum & ":F" & RowNum + 6 & ")"
        RowNum = RowNum + 7
    Next c
End Sub

 

[jc.021286]

quick and easy
use this formula

in N9
=average(offset('Daily Actual Data'!$F$14,Row(A1)*7,0,7,1))
fill down, this will use the Row(A1) to retrieve a spacing increment of the row number, thus 1, next line will be 2, then 3.

This will allow the formula to shift down 7 rows at a time and will average the next 7x1 range from that point.

Regards,
jc

Edits: changed everything to sevens