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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!