CODEWITHSUNDEEP
excel
Miguel Moura
Miguel Moura

Reputation: 39364

Calculate the Average By Date

In column A I have dates and in column B I have values.

How to calculate the average of values whose date have Month = 4 and Year = 2012?

Upvotes: 0

Views: 15368

Answers (3)

xQbert
xQbert

Reputation: 35323

Seems like a good use for AverageIfs...

enter image description here

Upvotes: 5

Marcio
Marcio

Reputation: 1733

Okay, assuming that you want to follow method one, let's use 2010 as the year base (a non-leap year). This array formula will give you a result of 19th November:

=AVERAGE(DATE(2010,MONTH(A1:A7),DAY(A1:A7)))

When you type it into the formula bar you need to complete the entry with CTRL+SHIFT+ENTER (not just the usual ENTER) so that Excel surrounds the formula with parentheses { }. You then need to format the cell as a date, eg. dd mmm.

If there are blank cells in the range then the above formula would give an incorrect result so it would have to be adapted as follows (made more complicated):

=AVERAGE(IF(ISNUMBER(A1:A7),DATE(2010,MONTH(A1:A7),DAY(A1:A7))))

This is also an array formula.

Upvotes: 1

John Bustos
John Bustos

Reputation: 19544

There are MANY ways to do this, but here's one way:

=SUMPRODUCT(B1:B4,--(MONTH(A1:A4)=4),--(YEAR(A1:A4)=2013))/SUMPRODUCT(--(MONTH(A1:A4)=4),--(YEAR(A1:A4)=2013))

Of course, change the row numbers to match your data range

Upvotes: 1

Related Questions