Formula Not Working

rachel_thompson1

I'm trying to calculate the average time it takes our department to process 'Contract Review' or other individual inserted tasks from two columns: 'Category' and 'Type of Request'.

I have created a column that has the average days of the start and completion dates.

I figured out how to calculate the average using one column 'Category':

=AVERAGEIF(Category:Category, "Contract Review", [Total Number of Days]:[Total Number of Days]) - which populates the sum of 24.75.

I now need to add in another column to pull the average amount of days for 'Contract Review' from 'Category' AND 'Type of Request'. It is my understanding that AVERAGEIF does not work on multiple criteria. I've tried multiple formulas and nothing works - see below:

=AVG(COLLECT({Contract Review Average}, {Category}, "Contract Review", {Type of Request}, "Contract Review"))

=AVG(COLLECT(Days:Days, Category:Category, "Contract Review")) + AVG(COLLECT(Days:Days, [Type of Request]:[Type of Request], "Contract Review"))

=AVG(COLLECT([Contract Review Average]:[Contract Review Average], Category:Category, ="Contract Review", Type of Request:Type of Request, ="Contract Review"))

I'm sure it is user-error.

If this is not possible, I was going to try and calculate the 'Type of Request' average with the same formula I was successful with (by replacing 'Category' with 'Type of Request') and then calculate and average based off those two numbers, but I keep getting an #unparseable error.

Any tips?

Thanks!

Danielle Arteaga

So, I think that - whether you want to average just [Total Number of Days] for a Category or for a Category + Type of Request, you will always be using the [Total Number of Days] column as your range.

I'm going to assume you have a column called [Category] and another called [Type of Request].

This should work (but adjust the column names in brackets to match your exact column names)

=AVG([Total Number of Days]:[Total Number of Days], [Category]:[Category], "Contract Review", [Type of Request]:[Type of Request], "Contract Review")

Danielle Arteaga

So, I think that - whether you want to average just [Total Number of Days] for a Category or for a Category + Type of Request, you will always be using the [Total Number of Days] column as your range.

I'm going to assume you have a column called [Category] and another called [Type of Request].

This should work (but adjust the column names in brackets to match your exact column names)

=AVG([Total Number of Days]:[Total Number of Days], [Category]:[Category], "Contract Review", [Type of Request]:[Type of Request], "Contract Review")

rachel_thompson1

Danielle, you are amazing. That worked! Thank you SO very much!