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?

Tags:

• ✭✭✭✭✭✭

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,

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

• ✭✭

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}

• ✭✭✭✭✭✭

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}, "")

• ✭✭

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)

• ✭✭✭✭✭✭

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

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

• ✭✭

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?

• ✭✭✭✭✭✭

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?

• ✭✭

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

• ✭✭✭✭✭✭

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!