Duration Formula

tim.curtin · 01/24/24

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

Paul Newcome · 01/25/24

Are you able to provide some screenshots for context?

tim.curtin · 01/30/24

Hi @Paul Newcome

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.

Paul Newcome · 01/30/24

Are you getting an error message or an unexpected number?

tim.curtin · 01/30/24

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.