# AVGIF Formula Based on Date Range

Options
✭✭✭✭

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.'

Tags:

## Best Answer

• ✭✭✭✭✭✭
edited 02/15/23 Answer ✓
Options

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

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

## Answers

• ✭✭✭✭✭✭
Options

@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."

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭
edited 02/15/23
Options
• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
edited 02/15/23 Answer ✓
Options

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

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!