In the below AVERAGEIFS formula I am attempting to look for all rows for APPLES, PEARS and ORANGES in column A:A that have a value of RIPE in column B:B.
For the first two AVERAGEIFS for APPLES and PEARS, the average of column P:P works fine.
When I add the AVERAGEIFS for ORANGES, I get #DIV/0! as the result.
What I have determined is that the AVERAGEIFS for ORANGES does not find any matches and returns a value of "0" so that is screwing up the average calculation across APPLES, PEARS and ORANGES collectively.
How do I handle a "0" value so the formula ignores that value when returning the average result?
SAMPLE FORMULA
-------------------------------------
=SUM(
AVERAGEIFS(P:P,
A:A,"="&"APPLES"),
B:B,"="&"RIPE")
AVERAGEIFS(P:P,
A:A,"="&"PEARS"),
B:B,"="&"RIPE")
AVERAGEIFS(P:P,
A:A,"="&"ORANGES")
B:B,"="&"RIPE")
)
For the first two AVERAGEIFS for APPLES and PEARS, the average of column P:P works fine.
When I add the AVERAGEIFS for ORANGES, I get #DIV/0! as the result.
What I have determined is that the AVERAGEIFS for ORANGES does not find any matches and returns a value of "0" so that is screwing up the average calculation across APPLES, PEARS and ORANGES collectively.
How do I handle a "0" value so the formula ignores that value when returning the average result?
SAMPLE FORMULA
-------------------------------------
=SUM(
AVERAGEIFS(P:P,
A:A,"="&"APPLES"),
B:B,"="&"RIPE")
AVERAGEIFS(P:P,
A:A,"="&"PEARS"),
B:B,"="&"RIPE")
AVERAGEIFS(P:P,
A:A,"="&"ORANGES")
B:B,"="&"RIPE")
)