Add new criteria to existing AVERAGE/COLLECT formula

Angie Germain

I am expanding an existing tracker to include a completely new/different case type. I added a type column with a drop down menu of the two case types and I modified all of my artifacts and formulas to include the criteria for only TYPE = SUPPLIER ISSUE. I could use some advice on the formula below that calculates turnaround times from two different sheets. How/where do I add the criteria where I only want to calculate if TYPE = SUPPLIER ISSUE?  
.=AVG(COLLECT({MainTAT}, {CRMCompleted}, IFERROR(WEEKNUMBER(@cell), 0) = [Week Number]@row, {CRMCompleted}, IFERROR(YEAR(@cell), 0) = Year@row, {CRM}, =$[Issues by Arlie]$1), COLLECT({TAT-Comp}, {CRMComp-Comp}, IFERROR(WEEKNUMBER(@cell), 0) = [Week Number]@row, {CRMComp-Comp}, IFERROR(YEAR(@cell), 0) = Year@row, {CRM-Comp}, $[Issues by Arlie]$1))

Adam Murphy

I think you would add to the collect statements after $[Issues by Arlie]$1

Something like $[Issues by Arlie]$1, {Type}, "SUPPLIER ISSUE" should work. Hope this helps.

Angie Germain

@Adam Murphy This did not exclude the other case type (non-supplier issues).

Brian_Richardson

=AVG(COLLECT({MainTAT}, {CRMCompleted}, IFERROR(WEEKNUMBER(@cell), 0) = [Week Number]@row, {CRMCompleted}, IFERROR(YEAR(@cell), 0) = Year@row, {CRM}, $[Issues by Arlie]$1, {Type}, "SUPPLIER ISSUE"), COLLECT({TAT-Comp}, {CRMComp-Comp}, IFERROR(WEEKNUMBER(@cell), 0) = [Week Number]@row, {CRMComp-Comp}, IFERROR(YEAR(@cell), 0) = Year@row, {CRM-Comp}, $[Issues by Arlie]$1, {Type},"SUPPLIER ISSUE"))

Note you need to insert a new reference for {Type} that points at the Type column in your other sheet. I'm assuming here that the sheet that you have this formula on is different than the sheet where Type exists as a column of data and that Type is a column on the same sheet as your {MainTAT}, {CRMCompleted}, etc columns that are referenced.

Because your AVG function is averaging two collections together, you need to add the Type condition to both pieces of the AVG arguments ie =AVG ( COLLECT (Collection 1 w/ Type), COLLECT (Collection 2 w/ Type) )

Angie Germain

@Brian_Richardson Thank you so much! I did add the criteria to both sheet COLLECT formulas, but it comes up INVALID.

Brian_Richardson

Which INVALID error are you getting?

If #INVALID OPERATION, was the formula working before adding the additional reference?

If #INVALID REF did you insert the {Type} reference by clicking Reference Cells in Ajother Sheet? And if so, click each other reference and Wdit to ensure they are all pointing to columns.

Copy/paste your formula back in here and we can keep troubleshooting it.

Angie Germain

Hello @Brian_Richardson,

Thank you for your time! I received the #invalid operation error. Yes, it was working before I added the new criteria. I can try it again, but I'm not sure why that didn't work. I made perfect sense when I read it.

Brian_Richardson

Looking at your formula further, I think it's the criteria pieces of your COLLECT like

IFERROR(WEEKNUMBER(@cell), 0) = [Week Number]@row

That's not a criteria, that's a logical function, and you cannot bury that into a collect as a criteria. Or rather, you can, but it doesn't work well.

When I tried this on a simple sheet with 5 dates and COUNT(COLLECT(Date:Date,Date:Date,WEEKNUMBER(@cell)>1)) I got 1 every time, regardless of how many dates were listed that met the criteria. I'm pretty sure the COUNT was counting an error, but I couldn't see the error as you cannot have a COLLECT just "floating" by itself.

The point being, you can simplify and probably make this formula functional by adding a Week Number and Year column to your datasheet (the sheet that you're referencing in this formula) for the two date columns, and cleaning up the formula. That way you can also understand better where you might be getting an error.