AVG and COLLECT
I have a large data set that I need to average one column but based on 2 criteria. I've got the formula working for one of the criteria but am unsure how to add in the second one.
Scenario: Metrics for year to date; columns for Dept (ITDept), duration of ticket (Duration) and the month that the ticket was closed (Month). I need an average for each dept for each month. I will create one formula for each month so that I can graph by month and by dept.
Formula for 1 criteria that I got to work:
=AVG(COLLECT(Duration1:Duration1573, Month1:Month1573, "Jan"))
The above will give me the total for the last 8 months but what I need is for this now to add in the ITDept so I can determine the average duration of tickets for Jan for the Apps team; then the average duration for Feb and so on. How do I add that 2nd criteria of ITDept to the formula above?
Help Article Resources
Check out the Formula Handbook template!