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 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
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 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
-
Thank you! Your suggested formula worked!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!