Need to get a Running Average Total of Duration of Escalation Formula

Sandra Franceschi

I need to present in a widget a running average Number from Total of all Escalation, From In Progress to Resolved .

Genevieve P.

Hi @Sandra Franceschi

If you're looking to get an Average from the column titled "Duration of Escalation", the first thing you'll need to do is clear out any formula errors. If there's a formula error in one cell of the column, we won't be able to reference it in any other formula or it will produce the same error.

Try wrapping an IFERROR around whatever formula you have, like so:

=IFERROR(formula, ''")

This will ensure that the cell is blank instead of showing an error when one of your Date cells is blank.

The we can build a simple AVG formula in a summary field in this sheet, that you can use as a Metric Widget.

=AVG([Duration of Escalation]:[Duration of Escalation])

This will automatically exclude any blank cells.

However it looks like you want to show a Duration even if the task hasn't been completed yet, so there is no Resolved date. In this instance you can update the formula in your "Duration of Escalation" to include a duration that looks at the Start Date until TODAY().

You can either use the NETDAYS Function to find the total days between Today and the Start date:

IF([End Date]@row = "", NETDAYS([Start Date]@row, TODAY()), current formula)

Or you could use the NETWORKDAYS Function if you want to exclude non-working days:

IF([End Date]@row = "", NETWORKDAYS([Start Date]@row, TODAY()), current formula)

Let me know if this makes sense and works for you! If not, it would be helpful to know what your current formula is in that column.

Cheers,

Genevieve

Sandra Franceschi

Thank you for the Prompt response, and for the suggestion to eliminate the error, My management team want to have 1 average total of escalations from Open to close from all . Is that possible?

Genevieve P.

Hi @Sandra Franceschi

Do you mean COUNT of how many rows are in the sheet? (Which would be the number of escalations recorded in the sheet)

Sandra Franceschi

My apologies I'm doing a horrible job of explaining it:

If we have a total of 10 escalations and some were close in 1 day other in in 2 and some in 3 days, my manager is looking for a total average of the closing rate in days

Genevieve P.

Hi @Sandra Franceschi

Thank you for clarifying! So you are looking to Average the [Duration of Escalation], but only if the escalation is now "Closed"?

If you've added the IFERROR to your Duration formula, this should return a blank cell for any of the rows that do not have a "Resolved Date".

This means you should be able to simply Average the Duration of Escalation to find the Average for any row where there is a Resolved Date.

Did you try:

=AVG([Duration of Escalation]:[Duration of Escalation])