Calculating Average Work Days with Stipulations

fennerb

Hello,

I'm trying to calculate average work days that each command takes to return a request. However, it has to meet several other stipulations.

-"Type of Request" has to include "FOIA" (drop-down column multi-select)

-"Date Forwarded to Command #1" (in the formula it's "Date Fwd to C#1") is a date-only select, that sometimes isn't populated (average work days start here)

-"Date Request is Returned from Command #1" (in the formula is "Date Rtnd from C#1") is also a date-only select, that sometimes isn't populated (average work days end here)

-In this specific instance, the command would be "PERSONNEL" in Command #1. (all caps, locked to single drop-down select)

What I have is returning an "#INCORRECT ARGUMENT," and I've played with it for an hour.

=AVERAGEIF((NETWORKDAYS({Date Fwd to C#1}, AND(IFERROR(ISDATE(@cell), 0), {Date Rtnd from C#1}, AND(IFERROR(ISDATE(@cell), 0), AND(IF({Type of Request}, CONTAINS("FOIA", @cell), IF({Command #1}, HAS("PERSONNEL", @cell)))))))))

Paul Newcome

Try

=AVG(COLLECT({Range to Average}, {1st Criteria Range}, 1st criteria, {2nd Criteria Range}, 2nd criteria, ..............................)

fennerb

Okay, so I tried a mixture of things playing with this for a while. I used this:

=AVG(COLLECT({Date Fwd to C#1}, {Date Rtnd from C#1}, AND(NETWORKDAYS({Date Fwd to C#1}, {Date Rtnd from C#1}), AND(IF({Type of Request}, CONTAINS("FOIA", @cell), IF({Command #1}, HAS("PERSONNEL", @cell)))))))

and got "INVALID DATA TYPE" which probably means that I used wrong syntax somewhere.

I then tried this:

=AVG(COLLECT({Date Fwd to C#1}:{Date Rtnd from C#1}, AND(NETWORKDAYS({Date Fwd to C#1}:{Date Rtnd from C#1}), AND(IF({Type of Request}, CONTAINS("FOIA", @cell), IF({Command #1}, HAS("PERSONNEL", @cell)))))))

and got "UNPARSEABLE." I tried to show the ranges with colons, like I saw in some examples. No luck.

Paul Newcome

What exactly are you trying to average?

fennerb

I'm trying to find an average of workdays that it takes certain places to return requests. So, say Personnel has been sent 15 requests. Based on the dates in the "Sent..." and "Received..." columns, I'm trying to find the average response time in work days it took them to fulfill those 15 requests that were sent to them.

Paul Newcome

You would need to produce those numbers by row on the source sheet in another column. Then we can average that new column on the other sheet.