# 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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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

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

By the way,

do you have anything in cell D3??

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

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

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

Mark,

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

rgds

Replies
0
Views
87
Replies
17
Views
545
Replies
6
Views
401
Replies
1
Views
196
Replies
0
Views
225

1,221,199
Messages
6,158,479
Members
451,495
Latest member
Jatin Bhagdev

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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