Using AVG(COLLECT())

Juan Ferrero

Good afternoon, I'm trying to do the equivalent of '"AVERAGEIFS". I read about using the collect formula, but I'm having an error.

The formula I'm using is:

=ABS(AVG(COLLECT({Master Sheet - 1960 - Net}, {Master Sheet - 1960 - Date}, Date@row, {Master Sheet - 1960 - Info}, [Summary Info]@row)))

I'm trying to calculate the average with a "date" and "info" condition.

I know there are values that meet both conditions, but I can't get them.

Paul Newcome

What does this output?

=COUNTIFS({Master Sheet - 1960 - Net}, @cell <> "", {Master Sheet - 1960 - Date}, Date@row, {Master Sheet - 1960 - Info}, [Summary Info]@row)

Paul Newcome

What error exactly are you getting?

Juan Ferrero

Previously I was getting UNPARSABLE , but I fixed a spelling error.

Currently I'm getting a #divide by zero, which is odd because I know there are values that meet both conditions.

Paul Newcome

What does this output?

=COUNTIFS({Master Sheet - 1960 - Net}, @cell <> "", {Master Sheet - 1960 - Date}, Date@row, {Master Sheet - 1960 - Info}, [Summary Info]@row)

Juan Ferrero

I see now, thank you!.

The output is a number for some rows and "0" for others. Those that have 0 as output will result in #divide by zero with the previous formula.
Thank you, that's a nice way to double check

Paul Newcome

Those that output zero can be accommodated using an IFERROR function wrapped around your original formula:

=IFERROR(original formula, "")