AVGIF Formula Based on Date Range

eLIZo

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

@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

@eLIZo The syntax for the AVERAGEIF function is:

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

eLIZo

https://community.smartsheet.com/discussion/comment/362427#Comment_362427

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

Jeff Reisman

@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])