# Average Collect Issues

Options
✭✭

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!

• ✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options

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.

• ✭✭
Options

This worked! Thank you :)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!