pivot table question

[sony]

dear,
i have below data sheet, i made a pivot table to show the average volume of each container type by dstn (destination).
But the answer is not correct.
pls help

Book1
	A	B	C	D	E	F	G	H
1	containernbr	containertype	model	D20	D40	HC	Volume	Fdstn
2	A1	D20	X1	1			12	BCN
3	A1	D20	X2				6	BCN
4	A2	D20	X1	1			26	BCN
5	A3	D20	X1	1			15	BCN
6	A4	D40	X1		1		45	BCN
7	A4	D40	X2				12	BCN
8	A5	D40	X2		1		43	BCN
9	A6	HC	X2			1	55	RTM
10	A6	HC	X1			1	8	RTM
11	A7	HC	X1			1	70	RTM
12	A8	D20	X1	1			45	RTM
13	A8	D20	X1				2	RTM
14
15	AverageofVolume	Fdstn
16	containertype	BCN	RTM	Total
17	D20	14.75	23.50	17.67
18	D40	33.33		33.33
19	HC		44.33	44.33
20	Total	22.71	36.00	28.25
21
22	14.75iswrong
23	AverageforBCND20shouldbe(12+6+26+15)/3=19.66
Sheet1

 

[ElectricSkywalker]

Sony,

Why are you dividing by three???

Average = Sum of the parts divided my the number of parts

In your example:

12+ 6 + 26 + 15 = 59

59 / 4 = 14.75

 

[plettieri]

I cant't quite figure out your the content of your formular:

"Average for BCN D20 should be (12+6+26+15)/3=19.66"

It is maybe Average for BCN D20 should be (12+26+15+45)/4=24.50"

A little clarification to go further.

pll

 

[ElectricSkywalker]

By the way,

do you have anything in cell D3??

(i know sometimes i find I have accidently formatted the text to white)

 

[sony]

dear all,
let me explain further:
D20 is the container toye of twenty feet conatiner. D40 is forty feet container. etc.
so what i need is the average volume of each container type by each dstn (destination).

Average D20 BCN should be divided by 3 instead of 4, because there are only 3 containers of D20 type under BCN. A1 happened two times because A1 contains 2 models (X1,X2), so the file need to show in that way. In sum, total volume in A1 (1xD20) is 18 (12+6).

Therefore, for BCN D20 avg volume should be (12+9+26+15)/3; BCN D40 = 47 because only 1 container and its total volume is 47 ...

But if i used the pivot and avg function, it won't give me the ans i expect.

In D3 is nothing, because D20 column will only count the first occurrence of container nbr i.e. A1, no duplicate count there.

Actually I can get the result setting three formula in pivot table. which are
avg D20 = volume / D20
avg D40 = volume / D40
avg hc = volume / hc
but the table prompts many irrelavant info.
because avg D40 will not have result for container type D20 , as you can see. then the table become too bulky

test.xls
	A	B	C	D	E	F	G	H
1	containernbr	containertype	model	D20	D40	HC	Volume	Fdstn
2	A1	D20	X1	1			12	BCN
3	A1	D20	X2				6	BCN
4	A2	D20	X1	1			26	BCN
5	A3	D20	X1	1			15	BCN
6	A4	D40	X1		1		45	BCN
7	A4	D40	X2				12	BCN
8	A5	D40	X2		1		43	BCN
9	A6	HC	X2			1	55	RTM
10	A6	HC	X1			1	8	RTM
11	A7	HC	X1			1	70	RTM
12	A8	D20	X1	1			45	RTM
13	A8	D20	X1				2	RTM
14
15			Fdstn
16	containertype	Data	BCN	RTM	Total
17	D20	SumofavgD20	19.67	47.00	26.50
18		SumofAvgD40	#DIV/0!	#DIV/0!	#DIV/0!
19		Sumofavghc	#DIV/0!	#DIV/0!	#DIV/0!
20	D40	SumofavgD20	#DIV/0!	#DIV/0!	#DIV/0!
21		SumofAvgD40	50.00	#DIV/0!	50.00
22		Sumofavghc	#DIV/0!	#DIV/0!	#DIV/0!
23	HC	SumofavgD20	#DIV/0!	#DIV/0!	#DIV/0!
24		SumofAvgD40	#DIV/0!	#DIV/0!	#DIV/0!
25		Sumofavghc	#DIV/0!	44.33333333	44.33333333
26	TotalSumofavgD20		53.00	180.00	#DIV/0!
27	TotalSumofAvgD40		79.50	#DIV/0!	#DIV/0!
28	TotalSumofavghc		#DIV/0!	60	#DIV/0!
Sheet1

 

[Mark W.]

Book1
	A	B	C	D	E	F	G	H	I
1	containernbr	containertype	model	D20	D40	HC	Volume	Fdstn	Selector
2	A1	D20	X1	1			12	BCN	1
3	A1	D20	X2				6	BCN	0
4	A2	D20	X1	1			26	BCN	1
5	A3	D20	X1	1			15	BCN	1
6	A4	D40	X1		1	1	45	BCN	1
7	A4	D40	X2				12	BCN	0
8	A5	D40	X2		1		43	BCN	1
9	A6	HC	X2			1	55	RTM	1
10	A6	HC	X1			1	8	RTM	1
11	A7	HC	X1			1	70	RTM	1
12	A8	D20	X1	1			45	RTM	1
13	A8	D20	X1				2	RTM	0
14
15
16	Selector	1
17
18	AverageofVolume	Fdstn
19	containertype	BCN	RTM	GrandTotal
20	D20	17.66666667	45	24.5
21	D40	44		44
22	HC		44.33333333	44.33333333
23	GrandTotal	28.2	44.5	35.44444444
24
Sheet3

This message was edited by Mark W. on 2002-10-02 10:15

 

[sony]

Mark,

looks the BCN answer is ignored 6 in the A1.
which should be counted in total BCN D20 volume.

rgds