How to use AVG and COLLECT to average data based on multiple criteria?

StephenC

Goal: To average the estimated GP data in a column on a reference sheet based on the person assigned to the projects and the completion date being within a specific quarter. For example, I need to know the average estimated GP for all projects assigned to PersonXYZ, for March, April, and May.

I have the Average working for each criteria separately but when I try to combine them using the AVG(COLLECT)), it fails. Below are two formulas that work followed by the combined formula that does not work.

=AVERAGEIF({PM}, Abbreviation@row, {Estimated GP})

=AVERAGEIF({Completed Date}, OR(MONTH(@cell) = 3, MONTH(@cell) = 4, MONTH(@cell) = 5), {Estimated GP})

=AVG(COLLECT({Estimated GP}, {Completed Date}, OR(MONTH(@cell) = 3, MONTH(@cell) = 4, MONTH(@cell) = 5), {PM}, Abbreviation@row))

Error I receive is invalid data type.

All data from columns on single sheet.

Genevieve P.

Hi @StephenC

Your formula structure looks correct to me! An Invalid Data Type error can sometimes occur with the MONTH function if the range it's evaluating has blank cells or cells with text in them.

Try wrapping each MONTH function in an IFERROR Function to exclude those cells...like so:

IFERROR(MONTH(@cell), 0)

=AVG(COLLECT({Estimated GP}, {Completed Date}, OR(IFERROR(MONTH(@cell), 0) = 3, IFERROR(MONTH(@cell), 0)= 4, IFERROR(MONTH(@cell), 0) = 5), {PM}, Abbreviation@row))

Cheers,

Genevieve