Calculating average value with specific conditions

FM1

Board Regular
Joined
Jan 1, 2008
Messages
61
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")
 

Some videos you may like

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
Joined
May 10, 2018
Messages
40
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
Joined
Jan 1, 2008
Messages
61
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
Joined
May 10, 2018
Messages
40
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")}
 

Watch MrExcel Video

Forum statistics

Threads
1,108,652
Messages
5,524,106
Members
409,561
Latest member
ay123

This Week's Hot Topics

Top