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

ksnyder

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?

Paul Newcome

You will need to use an IFERROR on the formula in the Value column to replace the error with either a blank or some other text (below turns it blank).

=IFERROR(([Benefit to Data: Areas of Improvement Score]@row * [Benefit to Data: Positive Impact Score]@row) / 28 * {Beneficial Impact of Project to Data: Weight}, "")

KevAnalyst

ksnyder,

What is the formula used in the Value column? If you manage the error in the Value column to blank on error then your AVG(COLLECT()) should work as intended.

Am I understanding this correctly or is your AVG(COLLECT()) formula being using in the Value column?

Kev

ksnyder

Yes, the AVG(COLLECT) is being used on the Value column. The reason that I am getting the error message is that there are blank cells in some rows referenced by the formula, which produces the #NO MATCH error. However, the formula works perfectly in rows where there are no blank cells, so I know that the formula is correct. In the rows that contain blank cells, the blank cells are not a mistake - should be blank.

Here's the formula causing the #NO MATCH error. It's a weighted average of two other columns:

=([Benefit to Data: Areas of Improvement Score]@row * [Benefit to Data: Positive Impact Score]@row) / 28 * {Beneficial Impact of Project to Data: Weight}

Paul Newcome

You will need to use an IFERROR on the formula in the Value column to replace the error with either a blank or some other text (below turns it blank).

=IFERROR(([Benefit to Data: Areas of Improvement Score]@row * [Benefit to Data: Positive Impact Score]@row) / 28 * {Beneficial Impact of Project to Data: Weight}, "")

ksnyder

Actually, I made an error - that is not the formula causing the #NO MATCH error. Here is the formula causing the error:

=VLOOKUP([Benefit to Data: Magnitude of Impact]@row, {Benefit to Data Magnitude Score}, 2)

Paul Newcome

You would still need to wrap it in the IFERROR the same way as above.

=IFERROR(VLOOKUP(.............), "")

ksnyder

Ok, I tried that and it almost works. However, the "" produces a zero, not a blank, which creates an erroneous average. How can you get IFERROR to create a blank?

Paul Newcome

It should be producing a blank and not an error. The very end is the output.

"" (double quotes) = blank

Can you copy/paste the exact formula from your sheet directly to here and provide a screenshot of the cells?

ksnyder

I got it working. Not exactly why I was getting zeros there at first, but now it's producing blanks. Thanks so much!

Paul Newcome

Happy to help. 👍️