CODEWITHSUNDEEP
excel
shdotcom
shdotcom

Reputation: 157

How to use an increment an average formula by more than one row and show the result i n columns in excel?

This formula 

=AVERAGE(OFFSET(B$2:B$31,30*(ROWS(D$2:D2)-1),0))

calculates the average of 30 values and shows the results in rows. B$2:B$31 represents the first range I want to average, 30 is the number of rows to increment each time. The first average will be showed in rows. D2 is the start cell.

How can I increment 30 rows at a time and show the average in columns starting from column I2

How can I achieve this without having to manually type in the cell reference.

Upvotes: 0

Views: 63

Answers (1)

barry houdini
barry houdini

Reputation: 46331

Change to this formula in I2

=AVERAGE(OFFSET($B2:$B31,30*(COLUMNS($I2:I2)-1),0))

copy formula across

Upvotes: 2

Related Questions