Calculate the average difference of 2 columns, with conditions

[swanniek]

Hello!

Apologies if I don't word this correctly,

I have a spreadsheet, In column AB (Actual Finish) is either a data and time that a job was finished, or blanks.
In Column C, Is the start Date.
Column H is the Priority, limited to either P1, P2, P3 or P4

I have built a table with months across the top and Priority going down and I need a formula, please, which can tell me the average length that it took to close all of the P1's closed in January (for example). I like to think I'm clever enough at excel that I can then drag it to fill in the rest of the table. However, I do also need one that is just by month regardless of the priority.

Thank you all very much in advance for your help.

Katie

 

[mrblister]

Hi Katie,
Can you please post a sample layout of your sheet? I tried laying it out from your description, but I think I'm misunderstanding something.

 

[swanniek]

I'm just trying to work out how to, apologies and thanks for the response..

 

[swanniek]

 

[PaddyD]

Why not just add a field to the core data table that is the length per record:

=AB2-C2

etc, then just use a pivot table or two?

 

[mrblister]

I'm just trying to work out how to, apologies and thanks for the response..

What I do (it isn't the most elegant, but it works):

1. Click "reply" to a message.
2. Click "Go Advanced".
3. Copy and paste the cells from Excel into the reply message.
4. Then left click in the center of the cells you just pasted.
5. In the menu above the message, click "Table Properties".
6. In the Table Style selected "Full Grid"
7. Click OK.
8. (Then modify the table is you need to.)

 

[AhoyNC]

Maybe something like this:
Change ranges to match your data then copy down and across.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Start Date	End Date	Priority	*	Priority / Month	10	11	12
2	10/1/2016	10/5/16 4:00 PM	P1	*	P1	4.67	0.00	3.00
3	10/1/2016	10/16/16 10:00 AM	P2	*	P2	15.52	0.00	0.00
4	10/15/2016	10/30/16 3:00 PM	P2	*	P3	0.00	14.77	4.46
5	10/30/2016	11/12/16 12:00 AM	P3	*	P4	0.00	8.60	0.00
6	10/30/2016	11/15/16 1:00 PM	P3	*	*	*	*	*
7	11/12/2016	*	P4	*	*	*	*	*
8	11/7/2016	11/15/16 2:30 PM	P4	*	*	*	*	*
9	11/28/2016	12/1/16 12:00 AM	P1	*	*	*	*	*
10	11/30/2016	12/4/2016 11:00	P3	*	*	*	*	*
Sheet