# Calculating average value with specific conditions

#### FM1

##### Board Regular
Column A - 'Date Received' - contains date values
Column B - 'Status' - contains 'In Progress', 'Unassigned' and 'Closed' values
Column C - 'Product' - contains 'Product A', 'Product B', 'Product C', 'Product D' values
Cell D1 = Today's Date

I need two formulas, where the calculation will return an average value for time unassigned from the point the entry was received for the following two cases:

1. Total average time unassigned (Column B = "Unassigned) for ALL products in column C
2. Total average time unassigned, broken down by product (Column C = "Product A/B/C/D")

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Chameleon64

##### New Member
It's easy if you use a "helper" column (eg column F) which calculates the difference between today's date and the date received. Then you can use the AVERAGEIFS function as follows:

1. AVERAGEIFS(\$F:\$F,\$B:\$B,"Unassigned")
2. AVERAGEIFS(\$F:\$F,\$B:\$B,"Unassigned",\$C:\$C,"Product A")

#### FM1

##### Board Regular
It's easy if you use a "helper" column (eg column F) which calculates the difference between today's date and the date received. Then you can use the AVERAGEIFS function as follows:

1. AVERAGEIFS(\$F:\$F,\$B:\$B,"Unassigned")
2. AVERAGEIFS(\$F:\$F,\$B:\$B,"Unassigned",\$C:\$C,"Product A")

I did have this in mind but was trying to avoid it since the worksheet is already very data heavy.

#### Chameleon64

##### New Member
I did have this in mind but was trying to avoid it since the worksheet is already very data heavy.
Perhaps an array formula then (using CTRL-SHIFT-ENTER when entering the formula):

1. ={SUMPRODUCT(\$D\$1-\$A\$2:\$A\$1000,--(\$B\$2:\$B\$1000="Unassigned"))/COUNTIFS(\$B\$2:\$B\$1000,"Unassigned")}

2. ={SUMPRODUCT(\$D\$1-\$A\$2:\$A\$1000,--(\$B\$2:\$B\$1000="Unassigned"),--(\$C\$2:\$C\$1000="Product A"))/COUNTIFS(\$B\$2:\$B\$1000,"Unassigned",\$C\$2:\$C\$1000,"Product A")}

Replies
1
Views
27
Replies
2
Views
44
Replies
1
Views
38
Replies
1
Views
47
Replies
7
Views
76