Formula Error multiple criteria

amanda.mcnab

Hello,

I am trying to find the average audit score for a range of percentages between two dates. There are divider rows for organization by month. I have been trying to utilize the following formula but continue to get #DIVIDE BY ZERO for an answer:

=AVG(COLLECT([Audit Score]:[Audit Score], [Date of Chat]:[Date of Chat], >=DATE(2025, 10, 1), [Date of Chat]:[Date of Chat], <=DATE(2025, 12, 31), [Audit Score]:[Audit Score], ISNUMBER(@cell)))

What did I miss?

Naeem Ejaz

Your formula is very close, but the #DIVIDE BY ZERO error means that COLLECT is returning no values (empty range), so AVG has nothing to calculate. This usually happens because:

• Divider rows (like month headers) have blank or non-numeric values in [Audit Score].
• The ISNUMBER(@cell ) condition is correct, but if all rows in the date range fail the criteria, the result is empty.

Try this version:

=IFERROR(AVG(COLLECT([Audit Score]:[Audit Score],[Date of Chat]:[Date of Chat], >=DATE(2025, 10, 1),[Date of Chat]:[Date of Chat], <=DATE(2025, 12, 31),[Audit Score]:[Audit Score], ISNUMBER(@cell ))), 0)

• Added IFERROR(..., 0) so if no rows match, it returns 0 instead of an error.

• Your logic is fine; the issue is likely no numeric values in the range or date mismatch.