Average with multiple criteria

Faheem

Screenshot 2024-02-16 at 6.03.47 PM.png

I am trying to get the average of a column named "Average working Days" while the "STATUS" = "On Hold", "In Progress" and "Not Started" and when I tried it, it gives me #Divide by zero.

=AVG(COLLECT({Request Tracker with Form Range - Average}, {Request Tracker Range 3}, Category@row, {Request Tracker Range 2 - Status}, "Not Started", {Request Tracker Range 2 - Status}, "In Progress", , {Request Tracker Range 2 - Status}, "On Hold"))

KPH

Hi

There are a couple of problems with the formula.

1. There is an extra comma after “In Progress”.
2. With a COLLECT function, all criteria must be met to be collected. So this will only return rows where {Request Tracker Range 2 - Status} is equal to “Not started” and also equal to “In progress”, and also…. Which is not possible. A row will have just one of those things in. You can use an OR function within the COLLECT.

Try

=AVG(COLLECT({Request Tracker with Form Range - Average}, {Request Tracker Range 3}, Category@row, {Request Tracker Range 2 - Status}, OR(@cell = "In Progress", @cell = "On Hold", @cell = "Not Started")))