AVG and COLLECT

Pamela Wagner

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?

Kelly Moore

Hey @Pamela Wagner

The approach I would use is dependent on what functionality I have available with my license. If I had Pivot App, I would be setting this up as a Pivot table. I would first create a Report with all the data I need -if I didn't have a Year column in my source sheet, I would add one so I can filter it by year. (In fact, in addition to having a 'Year' column I typically add an additional checkbox helper called 'IsYear' so I can dynamically pull the current Year data into reports.) I'm also assuming you have either created a Month Number column and/or transposed that number into your 'Jan' text. In other words, the Month closed is not just a raw date field. If using the Pivot App, I would use the report I just created as the source of the Pivot. I would not be using the actual sheet since I wouldn't be able to later filter by Year.

If I didn't have the Pivot app, I would set up a metrics sheet. From what I see of your current formula, I'm assuming you are using a Summary Field. You can do this, but you are will have to create 12x the number of Depts.

My metric sheet would look something like this - with columns for each month of the year

Current Month Formula = MONTH(TODAY())

Year formula = (YEAR(TODAY()))

This might change depending on how you want the data reported - are you looking at the current month or are you looking one month back. If looking back, January would have to take into account the crossing of the year.

The Jan formula (assuming looking at current month)

=IFERROR(AVG(COLLECT({Source Sheet Duration column}, {Source Sheet Year column}, IFERROR(@cell, 0) = Year@row, {Source Sheet Month column}, "Jan", {Source sheet Dept column}, Dept@row)), 0)

The February and beyond formula is

=IF([Current Month]@row >= 2, IFERROR(AVG(COLLECT({Source Sheet Duration column}, {Source Sheet Year column}, IFERROR(@cell, 0) = Year@row, {Source Sheet Month column}, "Feb", {Source sheet Dept column}, Dept@row)), 0)). The IF at the beginning keeps the months blank if they haven't been reached yet. If you are ok with zeros, you don't need the IF.

Would any of the above work for you? Don't hesitate to ask for more details if needed

Kelly

Pamela Wagner

I like your idea of using the Pivot app which I do have and need to learn how to use. Unfortunately you are bringing in some things that I'm not using. Really I just need to understand how to use another criteria with the COLLECT formula along with my column titles.

Kelly Moore

Hey @Pamela Wagner

When you say you need to understand how to use another criteria with your COLLECT- are you referring to your original question in this post?

=AVG(COLLECT(Duration1:Duration1573, Month1:Month1573, "Jan",Dept1:Dept1573, "ITDept"))

If you don't have to restrict the range to only the rows 1:1573, don't. If you want the range to grow as more rows are added then use the entire column range, ie, Duration:Duration

Kelly

Pamela Wagner

Thanks @Kelly Moore! My constraint is not the rows but understanding how to add that final criteria of ITDept. I thought when I tried to tell it to use the full columns and not bound by the specific rows that it didn't work. When I constrained it to 1:1573 then it worked. Weird but it did. Then I didn't understand how to add that final criteria. I can do basic formula but when I start adding additional criteria to it I get stuck.

Kelly Moore

Hey @Pamela Wagner

When you tried to omit the specific row numbers - did you omit the row numbers in both of your criteria ranges? For functions like Collects, SUMIFS, COUNTIFS, etc, the ranges of criteria must match one.

You can also make sure that the range you are 'collecting' are all numbers. This is required since you are performing an arithmetic function (AVG) on the values you are collecting.

Try this. Note I added an additional criteria, which means I had to include the additional range, even though this is the same column as the collect. Always think of the COLLECT syntax as =COLLECT(<collect range>, <criteria1 range, criteria1>,<criteria2 range, criteria2>,<criteria3 range, criteria3>, <etc>, <etc>). You always add criteria into a Collect as a range, criteria pair.

=AVG(COLLECT(Duration:Duration, Duration:Duration, ISNUMBER(@cell), Month:Month, "Jan",Dept:Dept, "ITDept"))

Kelly