Forum Discussion
Collect average from a range depending on dates
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
10 Replies
- u can use averageif(range, criteria=year)
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 ";".
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.
- 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.
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.
