AVERAGEIF and Quarters

Amy Evans

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

Kelly Moore

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 range-criteria 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

Kelly Moore

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 range-criteria 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

Amy Evans

Thank you! Your suggested formula worked!!!