CODEWITHSUNDEEP
exceldateaverage
Jason
Jason

Reputation: 75

Average Values from Specific Dates

I have a column of dates in the form 2011-05-18 (yyyy-mm-dd), and have a column of strictly digits.

I have tried using the averageif function:

=AVERAGEIF(A:A,YEAR=2012,N:N)

but this always returns #DIV/0! , even if that year does exist in the dataset.

I want to be able to compile a list of averages for each year (i.e. yearly average), and plot this data in a year vs. average values graphs. I do not want to have to manually determine averages for each year :( Any ideas?

TIA

Upvotes: 0

Views: 155

Answers (2)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15551

As per Tolerant average (ignore #NA, etc.) (which is even more demanding than the present case),

=AVERAGE(IF(YEAR(A:A)=2012,N:N))

with Ctrl+Shift+Enter works (tested).

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166126

I'm sure there's a cleaner way, but this works for me:

=SUM((YEAR(A:A)=2013)*N:N)/SUM((YEAR(A:A)=2013)*1)

entered as an array formula using Ctrl+Shift+Enter

Upvotes: 0

Related Questions