Average Collect Issues

lugo

Hello! I'm trying to write a formula that will average the amount of time a type of request takes to complete by month. I'm referencing another sheet in this formula and just can't get it to work. Here's what I have so far:

=AVG(COLLECT{Hours to Complete}, ISNUMBER(@cell), {Request Type}, "Quote Request", {Submitted Date}, YEAR(@cell) = 2023, {Submitted Date}, MONTH(@cell) = 1)

I'm not sure if I need the "IS NUMBER" or not, but I don't want it to count blank cells.

Anyone have ideas? Thanks in advance!

Devin Lee

The ISNUMBER isn't needed because the AVG function omits blanks.

=IFERROR(AVG(COLLECT([Hours to Complete]:[Hours to Complete], [Request Type]:[Request Type], "Quote Request", [Submitted Date]:[Submitted Date], AND(VALUE(YEAR(@cell)) = 2023, VALUE(MONTH(@cell)) = 3))), "")

=IFERROR(AVG(COLLECT({Hours to Complete}, {Request Type}, "Quote Request", {Submitted Date}, AND(VALUE(YEAR(@cell)) = 2023, VALUE(MONTH(@cell)) = 3))), "")

If you have any missing Submitted Dates there might be an error so make sure they are all filled in.

Devin Lee

The ISNUMBER isn't needed because the AVG function omits blanks.

=IFERROR(AVG(COLLECT([Hours to Complete]:[Hours to Complete], [Request Type]:[Request Type], "Quote Request", [Submitted Date]:[Submitted Date], AND(VALUE(YEAR(@cell)) = 2023, VALUE(MONTH(@cell)) = 3))), "")

=IFERROR(AVG(COLLECT({Hours to Complete}, {Request Type}, "Quote Request", {Submitted Date}, AND(VALUE(YEAR(@cell)) = 2023, VALUE(MONTH(@cell)) = 3))), "")

If you have any missing Submitted Dates there might be an error so make sure they are all filled in.

lugo

This worked! Thank you :)