pivot table question

sony

Board Regular
Joined
Jun 15, 2002
Messages
126
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
Joined
May 27, 2002
Messages
112
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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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

Board Regular
Joined
May 27, 2002
Messages
112
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
Joined
Jun 15, 2002
Messages
126

ADVERTISEMENT

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
Joined
Feb 10, 2002
Messages
11,654
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
Joined
Jun 15, 2002
Messages
126
Mark,

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

rgds
 

Forum statistics

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

Which adblocker are you using?

Disable AdBlock

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
Top