AVGIF Formula Based on Date Range

eLIZo · 02/15/23

Hello everyone,

I'm trying to get a average of a column if the cells in another column are within 90 days of today.

My current formula is =AVERAGEIF([Pick-up Variance]:[Pick-up Variance], [Pick-up Date]@row>TODAY(-90)).

I tried using AVG(Collect) as well with no luck.

=AVG(COLLECT([Pick-up Variance]:[Pick-up Variance], [Pick-up Date]:[Pick-up Date], , but I'm getting a Invalid Operation.'

Jeff Reisman · 02/15/23

@eLIZo That error message indicates an issue with the operator for the TODAY value. Let's try this, it seems to work in my test sheet:

=AVERAGEIF([Pick-up Date]:[Pick-up Date], @cell >TODAY(-90), [Pick-up Variance]:[Pick-up Variance])

Jeff Reisman · 02/15/23

AVERAGEIF(range, criterion, [range to average])

So your formula should be something like:

=AVERAGEIF([Pick-up Date]:[Pick-up Date], >TODAY(-90), [Pick-up Variance]:[Pick-up Variance])

"Give me the average of the Pick-up Variance values in all the rows where the Pick-up Date is within the past 90 days."

eLIZo · 02/15/23

eLIZo · 02/15/23

Yeah, I created the formula that way first and received a INVALID OPERATION. Tried it again and same thing

Jeff Reisman · 02/15/23

@eLIZo That error message indicates an issue with the operator for the TODAY value. Let's try this, it seems to work in my test sheet:

=AVERAGEIF([Pick-up Date]:[Pick-up Date], @cell >TODAY(-90), [Pick-up Variance]:[Pick-up Variance])

Best Answer