Mar 14 2021 08:30 PM
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
Mar 14 2021 09:41 PM
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.
Mar 14 2021 10:30 PM
Mar 14 2021 10:57 PM
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.
Mar 15 2021 02:36 AM
Mar 15 2021 03:42 AM
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 ";".
Mar 15 2021 03:48 AM
Mar 15 2021 03:51 AM
SolutionMar 15 2021 03:55 AM
Feb 22 2022 11:51 AM - edited Feb 22 2022 11:57 AM
@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
Mar 31 2022 03:03 AM - edited Mar 31 2022 03:04 AM
@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!
Mar 15 2021 03:51 AM
SolutionThat's like
=AVERAGEIFS(I:I, A:A, ">=" & DATE(...), A:A, "<=" & DATE(...) )