How to Average(Collect()) while ignoring error messages

I would like to average the entire Value column whenever Customer is 'Y'. I've been able to successfully do if there are no formula errors by using the following:

=AVG(COLLECT(Value:Value, Customer:Customer, true))

However, I discovered if there is an error in a value cell, the formula fails (it throws #NO MATCH error message). I would like it to calculate the average, while just excluding the cells that are #NO MATCH. Here's a snippet of the sheet.

I tried adding an IFERROR criterion to the COLLECT function, but still get a #NO MATCH error.

=AVG(COLLECT(Value:Value, Customer:Customer, true, Value:Value, IFERROR(Value@row, 0) <> 0))

Any ideas?


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!