CODEWITHSUNDEEP
excelexcel-formulaaverage
Mac Mallory
Mac Mallory

Reputation: 1

Excel function for averaging over an increment

I have a large table in excel with Column A running from 0 to 120 with increments of ~0.08 (This puts the last cell at 1441). I need to condense the table into increments of just 1. I need a function that can average the values from 0-1 and then be able to drag that function down so that the next cell references the next ~12 cells after the first. For Example: I need to average values from A1:A12, then when I drag the formula down 1 cell, it averages A13:A25, and so one.

Sample Data

Upvotes: 0

Views: 266

Answers (1)

xidgel
xidgel

Reputation: 3145

=AVERAGE(OFFSET($A$1,12*(ROW()-1),0,12,1))

ADDED IN RESPONSE TO SAMPLE DATA

In cell H2 to get averages of column B you can use the formula

=AVERAGE(OFFSET($B$1,12*(ROW()-2)+1,0,12,1))

In cell I2 to get averages of column C you can use the formula

=AVERAGE(OFFSET($C$1,12*(ROW()-2)+1,0,12,1))

And so forth.

Upvotes: 1

Related Questions