AVG(COLLECT with Multiple Criteria

Sam Lugiano

Hi,

I am trying to calculate the Average for the "Days Open" column using multiple criteria. Currently I am using two different formulas (below) to try and come up with one number. It would be great to use one formula instead of two.

Your help is much appreciated!

1. =AVG(COLLECT({Master Case List Days Open}, {Open/Closed}, "Open", {Date Referred}, <DATE(2022, 1, 1), {Attorney}, "Bob"))
2. =AVG(COLLECT({Master Case List Days Open}, {Master Case List Fee Rec'd/File Close Date}, >DATE(2020, 12, 31), {Master Case List Fee Rec'd/File Close Date}, <DATE(2022, 1, 1), {Attorney}, "Bob"))

Jeff Reisman

@Sam Lugiano

The COLLECT function can use many criteria. You're already using three criteria in the first one, why not just add more?

=AVG(COLLECT({Master Case List Days Open}, {Open/Closed}, "Open", {Date Referred}, <DATE(2022, 1, 1), {Master Case List Fee Rec'd/File Close Date}, >DATE(2020, 12, 31), {Master Case List Fee Rec'd/File Close Date}, <DATE(2022, 1, 1), {Attorney}, "Bob"))

Sam Lugiano

Thanks for the reply.

I tried that formula but I got a #DIVIDE BY ZERO error message. I then tried adding IFERROR and I got a #INCORRECT ARGUMENT SET message.

What may be happening is that the beginning of my formula is requesting "Open" cases but the end of the formula is asking for "Close Date" which lists as "Closed". I also tried a couple of AND(OR but did not work.

Sam Lugiano

After a day to think about it, turns out the formula was much simpler than I thought.