Average between date ranges

BFuller

Hello,

I need to find the average netdays between a service request date and an activity date if the service request date and the activity date fall between my dashboard start date and end date, and specific to a name@row. For example, I have 12 service Techs who get requests for repair work. When they fill out the form, they enter the requested date and the date they actually do the work. I need to find the average for a given date range. I set up my dashboard calculations page with a start and end date. I figured out how to do it for the YTD, but can't get it to calculate for me with additional criteria. Here is the formula that is working: =AVERAGEIF({FSE}, Name@row, {OOB Days to Close})

Here's what I am trying to do: =Averageif({FSE}, Name@row, {OOB Days to Close}, {Activity Date}, >=$[Beginning Date]$1, {Activity Date}, <=$[Ending Date]$1).

I wonder if a =netday function might work. Maybe with a nested if/then? I just need it to go average the days between two dates (service date and activity date) that are between my beginning date and end date and specific to the service tech (FSE). Thanks in advance for this help!

Genevieve P.

Hi @BFuller

An AVERAGEIF with multiple criteria would actually be a combination of AVG and COLLECT.

Ex:

=AVG(COLLECT({Column to Avg}, {Column 1 with Criteria}, "Criteria 1", {Column 2 with Criteria}, "Criteria 2"))

So in your case, try something like:

=AVG(COLLECT({OOB Days to Close}, {FSE}, Name@row, {Activity Date}, >=$[Beginning Date]$1, {Activity Date}, <=$[Ending Date]$1))

Let me know if this works for you!

Cheers,

Genevieve