Excel Offset Formula using a Range

0
votes

I want to use OFFSET to get an average of a range of 7 cells, then hop one cell to the right and get the average of the next range of 7 cells, and continue.

I know how to do this with one cells:

enter image description here

But I'm struggling to change this formula to work with ranges. Image below shows the ranges i would like to show data for:

enter image description here

Any suggestions?

2
excelexcel-formulaoffset

2 Answers

3
votes

I prefer the Non Volatile INDEX to the Volatile OFFSET.

Volatile means it will recalculate EVERY TIME excel calculates regardless of whether the underlying data changes or not. So if this workbook is open, no matter if the calculation is initiated on a different worksheet or workbook, it will recalculate these cells.

The INDEX will only recalculate if the underlying data recalculates:

IN B4:

=AVERAGE(INDEX('5+000"s'!$53:$53,(ROW(1:1)-1) * 7 + 2):INDEX('5+000"s'!$53:$53,(ROW(1:1)-1) * 7 + 8))

Then copy down.

The 7 are the spacing, there are 7 cells per average.

The 2 and 8 are the first columns used. 2 for B and 8 for H.

Then as the formula is dragged down the column numbers references will jump by seven each time.

1
votes

Try this in B4:

=AVERAGE(OFFSET($B$53,0,ROW()-4,1,7))

You messed up with two last arguments of OFFSET() which should be height (1) and width (7) of the range you need.