Need to get Median out of this list.

[franswa3434]

Hey everyone,

I need to find a way to break a report that shows the number of orders for a customer and the number of days it took to process the orders, then find a way to get the mean for each individual order. For example. if a customer has 3 orders and it took 3 days, and it to find the median of 3, 3, and 3.

To further clarify, if we had 2 customers, in which the first had 3 locations that took 3 days to process, and the second had 1 order which took 10 days, I would need to find the median of 3, 3, 3, and 10.

Below is an example of 3 customers.

This is what I am provided.

Customer</SPAN>	# of Orders</SPAN>	Days to Process</SPAN>
ABC123</SPAN>	3</SPAN>	3</SPAN>
XYZ321</SPAN>	1</SPAN>	10</SPAN>
Qwerty</SPAN>	2</SPAN>	6</SPAN>

<TBODY>
</TBODY>

I need to somehow come up with a way, where instead of getting a median of 3, 10, 6 (median of 6), it would actually find it as if it were listed as 3, 3, 3, 10, 6, 6 (median of 4.5).

I also need to be able to add to the list customers, orders, and their intervals to this list as my customer base grows.

Also, I can add additional sheets to this list, as well as columns. Just not rows.

Please let me know if further clarificaiton is needed, as I don't think I explained it very well.

Thanks everyone!

 

[sheetspread]

All I can think of is expanding the range (sort of a variable cartesian/cross join) and then calculating the median. Quite a few macro and formula methods are available for this, here's a cool version by bosco_yip:

Excel 2010
	A	B	C	D	E	F	G	H
1	Customer	# of Orders	Days to Process			Customer	# of Orders	Days to Process
2	ABC123	3	3			ABC123	3	3
3	XYZ321	1	10			ABC123	3	3
4	Qwerty	2	6			ABC123	3	3
5						XYZ321	1	10
6						Qwerty	1	6
7						Qwerty	1	6
8
9								4.5
Sheet5

Cell Formulas
Range	Formula
F2	=LOOKUP(ROW(A1),SUMIF(OFFSET(B$1,,,ROW($1:$99),),"<>")+1,A$2:A$99)&""
G2	=VALUE(LOOKUP(ROW(B1),SUMIF(OFFSET(C$1,,,ROW($1:$99),),"<>")+1,B$2:B$99)&"")
H2	=VALUE(LOOKUP(ROW(C1),SUMIF(OFFSET(B$1,,,ROW($1:$99),),"<>")+1,C$2:C$99)&"")
H9	=MEDIAN(H2:H7)

 

[franswa3434]

Is there a way to automatically expand the range without me having to manually do it each time an entry is added to the list (and maybe your way does this, and Im just missing it due to naivity).

 

[sheetspread]

Just keep dragging the formula, and increase the 99 to a larger number if necessary.

 

[sheetspread]

A macro can do this too:

Excel 2010
	A	B	C
1	Customer	# of Orders	Days to Process
2	ABC123	3	3
3	XYZ321	1	10
4	Qwerty	2	6
Sheet5 (2)

Sub addrowsbyvalue()
Dim x%, y%
x = 2
Do While Cells(x, 1).Value <> ""
y = Cells(x, 2).Value
If y > 1 Then
Rows(x + 1).Resize(y - 1).Insert
With Range(Cells(x, 1), Cells(x, 3))
.Copy Destination:=.Offset(1, 0).Resize(y - 1)
End With
Else
End If
x = x + y
Loop
Cells(x + 1, 3).Formula = "=median(c2:c" & x - 1 & ")"
End Sub

Excel 2010
	A	B	C
1	Customer	# of Orders	Days to Process
2	ABC123	3	3
3	ABC123	3	3
4	ABC123	3	3
5	XYZ321	1	10
6	Qwerty	2	6
7	Qwerty	2	6
8
9			4.5
Sheet5 (2)

Cell Formulas
Range	Formula
C9	=MEDIAN(C2:C7)