How to calculate average in Excel for cells that are 102 rows apart?

Question

Consider the following Excel spreadsheet:

      A          B       C        D        E
   1  company  date    money    date    average
   2  1        03-14       6    03-14      4    (6 + 3 + 3) / 3
   3  1        03-15       2    03-15      6    (2 + 7 + 9) / 3
   4  1        03-16       5    03-16      3.33 (5 + 1 + 4) / 3
   5  1        03-17       1    03-17      5.67 (1 + 9 + 7) / 3
   6  2        03-14       3    .
   7  2        03-15       7    .
   8  2        03-16       1    08-05
   9  2        03-17       9
   10 3        03-14       3
   11 3        03-15       4
   12 3        03-16       4
   13 3        03-17       7

For 100 companies, I have a money value for each date between 03-14 and 08-05 (102 days in total, because I removed the weekends and holidays; the above spreadsheet is a shortened example). The data is structured as panel data. In column D I have listed all the dates and in E I want to calculate the average money-value for each date (see example calculations).

My question is: with what Excel formula for column E could I accomplish this?

The average for 03-14 would be the AVERAGE of cell D2 + D104 + D206 + ... D10202 (102 cells apart), the AVERAGE for 03-15 would be calculated as the average of D3 + D105 + D207 ... etc.

Who knows how to accomplish this?

barry houdini barry houdini · Accepted Answer · 2012-09-11T21:19:08

Which version of Excel are you using? In Excel 2007 or later you can use AVERAGEIF function, e.g. this formula in E2 copied down

=AVERAGEIF(B:B,D2,C:C)

In earlier versions of excel try SUMIF/COUNTIF like this

=SUMIF(B:B,D2,C:C)/COUNTIF(B:B,D2)

How to calculate average in Excel for cells that are 102 rows apart?

1 Answers