Calculating an Average Duration in Google Sheets With Parameters

userUwDl1 0
Viewed 51

I've been trying to get a Google Sheets formula working for this scenario for a while now to the point I can feel Google laughing at me.

Here's the current table I'm working with

Here are the ranges I'm working with

A Column - Time and date of first request

B Column - Time and date of first response

F Column - Type (In this example, the type is marked as "LMM Reminder"

How I calculated the total task in Column W is;

=COUNTIFS(F:F, "LMM Request", A:A, ">="&W14, A:A, "<="&X14)

Which uses a date range with the cells W14 and X14.

My question is, can anyone think of a way to calculate the average response time, taking into account that they need to be marked in Column F as "LMM Request", have column A be between the date ranges entered in W14 and X14, and then subtract the First Response Time (Column B) from the First Request time (Column A) to produce a duration, and then obtain the average of all obtained durations.

spreadsheet google-sheets-formula google-sheets
2 Answers

Use this

=COUNTIFS(F:F, "LMM Request", A:A, "<="&W14, A:A, ">="&X14)

enter image description here

enter image description here

Osm0

This assumes that the dates/times in columns A & B are in a standard MM/DD/YYYY HH:MM:SS format. There may be some necessary adjustments depending on the date format you are using if it is different.

=AVERAGE(FILTER({B:B-A:A},F:F="LMM Request",A:A>=W14,A:A<=X14))
LASheets0
Related