Duration Formula
Hello, Trying to recreate a formula i wrote in a previous role to track duration for a monthly average. My formula is written as so:
=IFERROR(SUMIFS({Duration}, {Duration}, ISNUMBER(@cell), {End}, ISDATE(@cell), {End}, (IFERROR(MONTH(@cell), 0) = 1), {End}, (IFERROR(YEAR(@cell), 0) = 2024)) / COUNTIFS({Duration}, ISNUMBER(@cell), {End}, ISDATE(@cell), {Duration}, (IFERROR(MONTH(@cell), 0) = 1), {End}, (IFERROR(YEAR(@cell), 0) = 2024)), 0)
on my source sheet {Duration} is calculated in this formula =IFERROR(NETWORKDAYS([Initial Call]@row, [First Bill Emailed]@row), 0) / 12
My date column is in the date formatting. Not sure why I am getting no return on the first formula i should have an average for January as 1.96
Answers
-
Are you able to provide some screenshots for context?
-
Yes, this is the source sheet, with the columns utilized.
These are the references in the monthly average formulas i have been attempting.
Let me know if i can provide more for context.
-
Are you getting an error message or an unexpected number?
-
I am getting a return of 0 with my IFERROR built in, if removed it says Divide by Zero. I still think the formula is off. If i filter my source data on the sheet level to show for January data I get an average of 1.96 for what I am lookin to get a return average on.
-
Can you provide a screenshot of the filter settings you are applying?
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!