Collect average from a range depending on dates

[QWeelon]

Dear community,

I'm trying to find a neat formula to collect an average for all values corresponding to an arbitrary year, I have a column with dates and a column of corresponding values. I have a separate indata cell with the relevant year.

Basically, what I want to do is to collect values from all cells that corresponds to the date cells for my selected year and calculate the average of all the collected values. Note that I want to use entire columns as the range expands continuously. 

QWeelon

[#2]

@QWeelon 

Change your list to an Excel table (CTRL-T) and insert a pivot table.

Group the dates in years.

Years in rows area and values in values area.

Change the summarize function from Sum to Average.

[#3]

Hello,

Thank for your input - but that's not really a solution to my problem and does not fulfil my criteria. I want to be able to change year on a whim without thinking of changing regions.

The output value is on a different page so I want to minimise manual input.

[#4]

@QWeelon 

---

I want to be able to change year on a whim without thinking of changing regions.

The output value is on a different page so I want to minimise manual input.

---

What "regions" are you talking about?

The pivot table usually is on a different sheet. And there is no manual input. Just refresh the pivot table when new data has been added.

[#5]

u can use averageif(range, criteria=year)

[#6]

Hi Chahine,

I've tried your formula with some success, although I'm struggling a bit with the year criterion. I've managed to insert a lower limit and would like to complete it with an upper limit as well. Se figure below for formulas.

Column A contains dates and Column I contains the values of which i want to average.

Do you have any suggestions on how I can improve my criteria?

I'm using the Swedish input version in Excel, so commas "," are replaced with semicolons ";". 

[#7]

Hi Detlef,

I'm sorry I'm not clear enough, but I want a very general formula that don't require any extra imposition. Dates are arbitrary with a corresponding value. By regions, I mean that i want to use the entire columns, A:A instead of e.g. A1:A35.

Thank you again for your time and input.

[#8 · Accepted solution]

@QWeelon 

That's like

=AVERAGEIFS(I:I, A:A, ">=" & DATE(...), A:A, "<=" & DATE(...) )

[#9]

Works like a charm!

Thank you everyone!

[#10]

@QWeelon can you describe how it worked I am facing difficulty to follow the @Sergei Baklan function since my date column range depend on the available hourly records and i want to have average of wind over the day
this is my part of my data
Date                Wind(m/sec)
10/1/2010 0:00 3.61
10/1/2010 1:00 3.61
10/1/2010 2:00 1.56
10/1/2010 8:00 2.58
10/1/2010 9:00 3.08
10/1/2010 10:00 5.67
10/1/2010 11:00 2.06
10/1/2010 12:00 2.06
10/1/2010 14:00 4.64
10/1/2010 15:00 1.56
10/1/2010 17:00 4.11
10/1/2010 20:00 3.08
10/1/2010 23:00 3.61
10/2/2010 5:00 2.58
10/2/2010 8:00 2.58
10/2/2010 9:00 3.08
10/2/2010 10:00 3.08
10/2/2010 11:00 3.61
10/2/2010 12:00 1.56
10/2/2010 13:00 2.06
10/2/2010 14:00 1.03
10/2/2010 18:00 1.56
10/2/2010 20:00 1.56
10/2/2010 21:00 2.58
10/2/2010 22:00 3.08
10/3/2010 2:00 3.08
10/3/2010 6:00 2.58
10/3/2010 7:00 2.06
10/3/2010 8:00 2.58
10/3/2010 9:00 4.64
10/3/2010 10:00 5.67
10/3/2010 11:00 5.14
10/3/2010 12:00 4.64
10/3/2010 13:00 5.14
10/3/2010 14:00 1.56
10/3/2010 15:00 4.64
10/3/2010 16:00 2.58
10/3/2010 17:00 2.58
10/3/2010 18:00 3.08
10/3/2010 19:00 3.08
10/3/2010 20:00 3.08
10/3/2010 21:00 4.11
10/3/2010 22:00 3.61
10/4/2010 4:00 3.08
10/4/2010 5:00 2.58
10/4/2010 6:00 2.58
10/4/2010 7:00 3.61
10/4/2010 8:00 2.58
10/4/2010 9:00 3.61
10/4/2010 12:00 6.17
10/4/2010 13:00 1.56
10/4/2010 15:00 6.17
10/4/2010 16:00 6.17
10/4/2010 17:00 5.67
10/4/2010 19:00 3.08
10/4/2010 20:00 4.11
10/4/2010 22:00 3.08
10/5/2010 2:00 5.67
10/5/2010 5:00 4.64
10/5/2010 6:00 6.17
10/5/2010 7:00 6.69
10/5/2010 8:00 9.78
10/5/2010 9:00 8.75
10/5/2010 10:00 10.28
10/5/2010 13:00 6.17
10/5/2010 14:00 6.17
10/5/2010 15:00 5.14
10/5/2010 16:00 3.61
10/5/2010 17:00 5.67
10/5/2010 19:00 3.08
10/5/2010 22:00 3.61
10/5/2010 23:00 3.08

[#11]

@Wondimagegn I had a similar problem, which I solved from this video.https://www.youtube.com/watch?v=XIhbL20jTHc
In the video, the case for sumifs is used. Averageifs is same as sumifs. Beaware of using logical operators when dealing with date data.
I hope it helps!