Forum Discussion
Collect average from a range depending on dates
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 ";".
- Works like a charm!
Thank you everyone!QWeelon can you describe how it worked I am facing difficulty to follow the SergeiBaklan 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.08Wondimagegn 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!
