Average time calculation

[trapsy]

Hello,
I am stuck with one calculation and would appreciate some help.

The data which I am having is showing how many work orders of a particular month were finalized in 1st week of submission, 2nd week, 3rd week, etc. Any suggestions on how to calculate the average time (number of weeks) needed to finalize the order from it's submission?
Example of the data is below:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD]Week[/TD]
[TD][/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[/TR]
[TR]
[TD]Week0[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Week1[/TD]
[TD]1 week after order submitted (0-7)[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD]Week2[/TD]
[TD]2 weeks after order submitted (8-14)[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TD]Week3[/TD]
[TD]3 weeks after order submitted (15-21)[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]51[/TD]
[/TR]
[TR]
[TD]Week4[/TD]
[TD]4 weeks after order submitted (22-28)[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]Week5[/TD]
[TD]5 weeks after order submitted (29-35)[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]82[/TD]
[/TR]
[TR]
[TD]Week6[/TD]
[TD]6 weeks after order submitted (36-42)[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]147[/TD]
[/TR]
[TR]
[TD]Week7[/TD]
[TD]7 weeks after order submitted (43-49)[/TD]
[TD="align: right"]139[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]97[/TD]
[/TR]
[TR]
[TD]Week8[/TD]
[TD]8 weeks after order submitted (50-56)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Week9+[/TD]
[TD]9 weeks or more after order submitted (>57)[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TTL ORDERS[/TD]
[TD="align: right"]499[/TD]
[TD="align: right"]425[/TD]
[TD="align: right"]596[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!

 

[Tetra201]

Maybe this for monthly average:

=SUMPRODUCT(C2:C11,{0;1;2;3;4;5;6;7;8;9})/SUM(C2:C11)

 

[FormR]

Hi, welcome to the board!

Are these the results you were looking for?

Excel 2013/2016
	A	B	C	D	E
1	Week		January	February	March
2	Week0		1	1	8
3	Week1	1 week after order submitted (0-7)	33	28	27
4	Week2	2 weeks after order submitted (8-14)	33	30	110
5	Week3	3 weeks after order submitted (15-21)	60	34	51
6	Week4	4 weeks after order submitted (22-28)	38	67	56
7	Week5	5 weeks after order submitted (29-35)	103	94	82
8	Week6	6 weeks after order submitted (36-42)	89	111	147
9	Week7	7 weeks after order submitted (43-49)	139	59	97
10	Week8	8 weeks after order submitted (50-56)	3	1	14
11	Week9+	9 weeks or more after order submitted (>57)	0	0	4
12		TTL ORDERS	499	425	596
13
14			4.964	4.741	4.602
Sheet1

Cell Formulas
Range	Formula
C14	=SUMPRODUCT(C2:C11,ROW(C2:C11)-ROW(C2))/C12

 

[trapsy]

Thanks so much for the both of the replies!

Could you explain what does the formula actually do, i.e. what is the logic behind the calculation?

Is it fair to say that it takes on average 5 weeks for the order to be processed after its submission (based on Jan-Mar data)?

 

[FormR]

Could you explain what does the formula actually do, i.e. what is the logic behind the calculation?

Hi, it's "total weeks used" / "total orders"

"Total weeks used" being the sum of ("total orders" in the "0 weeks" category * 0, "total orders" in the "1 week" * 1, "total orders" in the "2 weeks" * 2 etc..)

Is it fair to say that it takes on average 5 weeks for the order to be processed after its submission (based on Jan-Mar data)?

For a total you should perform the same calculation on the total category by category: i.e.

Excel 2013/2016
	A	B	C	D	E	F
1	Week		January	February	March	Total
2	Week0		1	1	8	10
3	Week1	1 week after order submitted (0-7)	33	28	27	88
4	Week2	2 weeks after order submitted (8-14)	33	30	110	173
5	Week3	3 weeks after order submitted (15-21)	60	34	51	145
6	Week4	4 weeks after order submitted (22-28)	38	67	56	161
7	Week5	5 weeks after order submitted (29-35)	103	94	82	279
8	Week6	6 weeks after order submitted (36-42)	89	111	147	347
9	Week7	7 weeks after order submitted (43-49)	139	59	97	295
10	Week8	8 weeks after order submitted (50-56)	3	1	14	18
11	Week9+	9 weeks or more after order submitted (>57)	0	0	4	4
12		TTL ORDERS	499	425	596	1520
13
14					Average	4.759868
Sheet1

Cell Formulas
Range	Formula
F2	=SUM(C2:E2)
F14	=SUMPRODUCT(F2:F11,ROW(F2:F11)-ROW(F2))/F12