AVERAGEIF and Quarters
Hello,
I have two sheets:
Sheet 1  contains a column with "PROD Release Dates" and a 2nd column that contains a formula to calculate the "Cycle Time Months" it took to complete the work.
Sheet 2  contains 4 rows to show the Quarter start/end dates for 2023. You will see this is referenced as "[Quarter Date End]@row" in the formula below.
Formula Issue: I want to use AVERAGEIF to reference Sheet 1 and any date that falls within Q1, Q2, Q3, Q4, then I want to average the Months column.
The below formula doesn't work.
=AVERAGEIF({Cycle Time Months}, {PROD Release Date} <= [Quarter Date End]@row)
Thank you!
Amy
Best Answer

Hey @Amy Evans
The syntax smartsheet is expecting for an AverageIF is AVERAGEIF(range, criteria, [average_range]), where the first range is part of the rangecriteria pair, and the last range is what needs to be averaged, if it is a different range than the first. Note an AverageIf will only support one criteria. If there are more criterion required to filter the data, one must swap to AVG(COLLECT). The AverageIf works for your case.
AVERAGEIF({PROD Release Date},@cell<=[Quarter Date End]@row, {Cycle Time Months})
Does this formula give you the expected result?
Kelly
Answers

Hey @Amy Evans
The syntax smartsheet is expecting for an AverageIF is AVERAGEIF(range, criteria, [average_range]), where the first range is part of the rangecriteria pair, and the last range is what needs to be averaged, if it is a different range than the first. Note an AverageIf will only support one criteria. If there are more criterion required to filter the data, one must swap to AVG(COLLECT). The AverageIf works for your case.
AVERAGEIF({PROD Release Date},@cell<=[Quarter Date End]@row, {Cycle Time Months})
Does this formula give you the expected result?
Kelly

Thank you! Your suggested formula worked!!!
Help Article Resources
Categories
Check out the Formula Handbook template!