Forum Discussion
Solved
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...
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.
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.
- 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.