Trouble calculating regular averages in Excel

October 18, 2009 8:40 PM

In Excel, how do I calculate the average of every 300 data points in a large data set?

I have ~12000 data points in Excel. I want to take the average of every 300 points (ie. Average(A1:A300), Average(A3001:A6001)). If I write the first formula out, or even write the first few iterations, then drag and fill to complete the rest, it doesn't follow my pattern. It just takes the average starting from the next row in the data (not the row 300 down). As far as my Excel knowledge takes me I'll have to write the formula out 40 times each time I want to analyze a different data set.

Is there a quicker way for Excel to do this?

posted by Midnight Rambler to computers & internet (10 comments total)

You could use vbscript. Something off the top of my head:

Public Sub averageEveryThreeHundred()
Dim intRow As Integer
intRow = whatever the starting row is
Dim lngTotal As Long
Dim intCount As Integer
Dim lngAverage
Do While IsEmpty(Range("column" & intRow).Value = "False"
lngTotal = lngTotal + Range("column" & intRow).Value
intRow = intRow + 300
intCount = intCount + 1
Loop
lngAverage = (lngTotal / intCount)
MsgBox("Counted " & intCount " values, totalled to " & lngTotal & " and averaged to " & lngAverage)
End Sub

To make it put that final average into a cell, add in Range(target).Value = lngAverage
posted by battlebison at 9:11 PM on October 18

If I'm understanding you correctly, I think you want the "offset" function. It lets you define a range of cells based upon one cell, a row/column offset from that cell, and a height/width.

So you have 12000 cells, all in column A, A1:A12000.

In cell B1, enter "0". In cell B2, enter "=B1+300". Fill column B down, starting from B2. So column B is 0, 300, 600, 900, et cetera.

In cell C1, enter "=average(offset($a$1, b1, 0, 300, 1))".

That is, "average the range of cells that starts B1 rows and zero columns away from cell A1, and which includes 300 rows and one column".

Then fill down column C.
posted by Flunkie at 9:12 PM on October 18

I would grab some spare space (in this example, I'm starting in cell G1, for no very good reason, and assuming your data points are in column A, starting at 1), and do as follows:

In cell G1: 1
in cell G2: =G1+300

In cell H1: =AVERAGE(INDIRECT("A"&G1):INDIRECT("A"&299+G1))

and then fill down as far as you fancy.
posted by pompomtom at 9:14 PM on October 18

Err, forgot to include an "&" after intCount on the 2nd last line, and a ")" after Value on the Do While line.

Also should mention that you can do this in Office 2007 by going to View -> Macros, type something in, hit "Create". This should bring up Visual Basic Editor, just copy and paste that code and fill in what I put in italics. In earlier versions, find Macros under Tools.
posted by battlebison at 9:18 PM on October 18

Haha, on re-read, I totally missed the question. I thought you wanted the average of every 300 points of data. You want to average the data at 300 number intervals. Or something like that. I get what you want, and what I put is not it. Too tired, time for bed.
posted by battlebison at 9:20 PM on October 18

What do it semi-manually. Throw in the formula for 1:300 in C300 and 301:600 in 600, leaving the rest of the column blank. Then select the range between C300 and C600 using the keyboard (ctrl+shift+down) and hit ctrl-C to copy. Release the keys. Then hit the down arrow and then hit the up arrow to return the cursor to C600. Hit ctrl-V to paste. Hit ctrl-down arrow to get to C900. Rinse and repeat.
posted by sachinag at 10:01 PM on October 18

A clearer explanation of the procedure sachinag uses:

Data are in one column and results in another, for example?

All right then. Leave 299 out of every 300 results cells blank. In the 300th cell, place your formula. Select cells 1 through 300 of your results column. Fill that down. (Various clever copying-and-pasting exercises can also be done. On Mac, Option-dragging works too.) Relative cell addresses will take care of averaging the correct cells.

What is the trick here? The trick here is that blank cells can be filled into other cells, though apparently only if one cell in the block is nonblank.
posted by joeclark at 10:11 PM on October 18

Add two columns. I'll assume A and B (to the left of your data.) Starting at row 2, assuming you have column headers; put "incr" in A1 and "section" in B1.

Cell A2 and B2 are 0.

A3 = A2+1
B3 = IF(mod(A3,300)==0,b2+1,b2)

This will cause B3 to steadily increase every 300 items.

Then do a pivot table. Group on "section." Done.
posted by joshu at 11:12 PM on October 18

Excel figures out the source cells in a formula by position relative to the cell containing the formula, so in order to move the start cell by 300 you must move the formula cell by 300. You can do a 'fill series' downward, and it will effectively copy the cells from the first cell in the series down to the last cell with data and repeat them until the end of the highlighted zone. So if your data set starts at A1, you create average(A1:A300) in field B300, and then highlight from B1 down to the end of the data set and 'fill series'.

Then you select the B column, copy and "paste special" values only, and sort B column in order to get rid of all the blank space. If you want to keep the averages in the same order, you can also put "1" in C300 and include the C column in your 'fill series'.
posted by Lady Li at 12:19 AM on October 19

Flunkie's answer was the first that was exactly what I was looking for. Thanks a lot everyone.
posted by Midnight Rambler at 6:56 AM on October 19