# pivot table question

#### sony

##### Board Regular
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
ABCDEFGH
1containernbrcontainertypemodelD20D40HCVolumeFdstn
2A1D20X1112BCN
3A1D20X26BCN
4A2D20X1126BCN
5A3D20X1115BCN
6A4D40X1145BCN
7A4D40X212BCN
8A5D40X2143BCN
9A6HCX2155RTM
10A6HCX118RTM
11A7HCX1170RTM
12A8D20X1145RTM
13A8D20X12RTM
14
15AverageofVolumeFdstn
16containertypeBCNRTMTotal
17D2014.7523.5017.67
18D4033.3333.33
19HC44.3344.33
20Total22.7136.0028.25
21
2214.75iswrong
23AverageforBCND20shouldbe(12+6+26+15)/3=19.66
Sheet1

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### ElectricSkywalker

##### Board Regular
Sony,

Why are you dividing by three???

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

12+ 6 + 26 + 15 = 59

59 / 4 = 14.75

#### plettieri

##### Well-known Member

"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

##### Board Regular
By the way,

do you have anything in cell D3??

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

#### sony

##### Board Regular

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
ABCDEFGH
1containernbrcontainertypemodelD20D40HCVolumeFdstn
2A1D20X1112BCN
3A1D20X26BCN
4A2D20X1126BCN
5A3D20X1115BCN
6A4D40X1145BCN
7A4D40X212BCN
8A5D40X2143BCN
9A6HCX2155RTM
10A6HCX118RTM
11A7HCX1170RTM
12A8D20X1145RTM
13A8D20X12RTM
14
15Fdstn
16containertypeDataBCNRTMTotal
17D20SumofavgD2019.6747.0026.50
18SumofAvgD40#DIV/0!#DIV/0!#DIV/0!
19Sumofavghc#DIV/0!#DIV/0!#DIV/0!
20D40SumofavgD20#DIV/0!#DIV/0!#DIV/0!
21SumofAvgD4050.00#DIV/0!50.00
22Sumofavghc#DIV/0!#DIV/0!#DIV/0!
23HCSumofavgD20#DIV/0!#DIV/0!#DIV/0!
24SumofAvgD40#DIV/0!#DIV/0!#DIV/0!
25Sumofavghc#DIV/0!44.3333333344.33333333
26TotalSumofavgD2053.00180.00#DIV/0!
27TotalSumofAvgD4079.50#DIV/0!#DIV/0!
28TotalSumofavghc#DIV/0!60#DIV/0!
Sheet1

#### Mark W.

##### MrExcel MVP
Book1
ABCDEFGHI
1containernbrcontainertypemodelD20D40HCVolumeFdstnSelector
2A1D20X1112BCN1
3A1D20X26BCN0
4A2D20X1126BCN1
5A3D20X1115BCN1
6A4D40X11145BCN1
7A4D40X212BCN0
8A5D40X2143BCN1
9A6HCX2155RTM1
10A6HCX118RTM1
11A7HCX1170RTM1
12A8D20X1145RTM1
13A8D20X12RTM0
14
15
16Selector1
17
18AverageofVolumeFdstn
19containertypeBCNRTMGrandTotal
20D2017.666666674524.5
21D404444
22HC44.3333333344.33333333
23GrandTotal28.244.535.44444444
24
Sheet3

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

#### sony

##### Board Regular
Mark,

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

rgds

Replies
4
Views
268
Replies
8
Views
747
Replies
2
Views
151
Replies
1
Views
349
Replies
6
Views
138

1,147,816
Messages
5,743,376
Members
423,790
Latest member
kevinlee_5

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back