Trying to use average formula of the DURATION with multiple criterias in the same sheet

Gabriel Morau

I want to check a Lead time AVR for on the Duration Collum for only specific STATUS :Pending Marsh, Pending Lockton, Pending Lockton and Marsh and Pending LL

I tried below formulas but are not working at all.

=AVERAGEIF([Status]:[Status], OR([Status]@row = "Pending LL", [Status]@row = "Pending Lockton and Marsh", [Status]@row = "Pending Lockton", [Status]@row = "Pending Marsh"), [Duration]:[Duration])

=AVG(COLLECT({Sephora - COI Tracking Range 3}, {Sephora - COI Tracking Range 2},"Pending Marsh", {Sephora - COI Tracking Range 2},"Pending LL")))

Paul Newcome

Your first AVERAGEIF should work if you replace each Status@row with @cell.

To use the AVG/COLLECT with cross sheet references, you would definitely need to use the OR function there as well.

=AVG(COLLECT({Range To Average}, {Criteria Range}, OR(@cell = "This", @cell = "That", @cell = "Somethign else")))

ChristianFinke

Hey @Gabriel Morau

I would try this formula.

=AVERAGE(COLLECT([Duration]:[Duration], [Status]:[Status], OR(@cell = "Pending LL", @cell = "Pending Lockton", @cell = "Pending Lockton and Marsh", @cell = "Pending Marsh")))

This formula collects the values from the Duration column where the Status matches one of the specified criteria and then calculates the average. If you still run into issues, check that the Status column contains exact text matches and that your Duration column uses numerical values (not text).

Let me know if that helps!

Paul Newcome

Your first AVERAGEIF should work if you replace each Status@row with @cell.

To use the AVG/COLLECT with cross sheet references, you would definitely need to use the OR function there as well.

=AVG(COLLECT({Range To Average}, {Criteria Range}, OR(@cell = "This", @cell = "That", @cell = "Somethign else")))

Gabriel Morau

@Paul Newcome And @Christian Thanks! it worked very well

Paul Newcome

Happy to help. 👍️