Explain Why this Pivot Table is Layed Out this Way

[bencar]

Hi guys,

Trying to figure out why this pivot table is layed out the way it is. Here are my 4 questions. Below that is the exercise and its info.

1. Why is 'design' in the column area?
2. Why are 'amusement park' and 'roller coaster' in the row area?
3. Why is 'type' in the filter section?
4. In creating pivot tables in the future where should I put data into which PT data fields??


Heres the exercise. And below is all the info:

Convert this data into a pivot table and find the overall average speed of all rides that satisfy the following criteria:

• The Type is Steel.

• The Design is Sit Down.

• The Amusement Park has the word adventure somewhere in the title.

[TABLE="width: 663"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Roller Coaster
[/TD]
[TD]Amusement Park
[/TD]
[TD]Type
[/TD]
[TD]Design
[/TD]
[TD]Status[/TD]
[TD]Opened[/TD]
[TD]Speed
[/TD]
[/TR]
[TR]
[TD]Air[/TD]
[TD]Alton Towers[/TD]
[TD]Steel[/TD]
[TD]Flying[/TD]
[TD]Operating[/TD]
[TD="align: right"]2002[/TD]
[TD="align: right"]46.6[/TD]
[/TR]
[TR]
[TD]Boomerang[/TD]
[TD]Pleasure Island Family Theme Park[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1993[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD]Cobra[/TD]
[TD]Paultons Park[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2006[/TD]
[TD="align: right"]31.1[/TD]
[/TR]
[TR]
[TD]Colossus[/TD]
[TD]Thorpe Park[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2002[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]Corkscrew[/TD]
[TD]Alton Towers[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1980[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Corkscrew[/TD]
[TD]Flamingo Land Theme Park & Zoo[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1983[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Crazy Mouse[/TD]
[TD]South Pier[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1998[/TD]
[TD="align: right"]29.1[/TD]
[/TR]
[TR]
[TD]Crazy Mouse[/TD]
[TD]Brighton Pier[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]29.1[/TD]
[/TR]
[TR]
[TD]Enigma[/TD]
[TD]Pleasurewood Hills[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1995[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD]Express[/TD]
[TD]M&Ds Scotland's Theme Park[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2006[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]Fantasy Mouse[/TD]
[TD]Fantasy Island[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]29.1[/TD]
[/TR]
[TR]
[TD]G Force[/TD]
[TD]Drayton Manor Park[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2005[/TD]
[TD="align: right"]43.5[/TD]
[/TR]
[TR]
[TD]Grand National[/TD]
[TD]Pleasure Beach, Blackpool[/TD]
[TD]Wood[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1935[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Infusion[/TD]
[TD]Pleasure Beach, Blackpool[/TD]
[TD]Steel[/TD]
[TD]Inverted[/TD]
[TD]Operating[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]49.7[/TD]
[/TR]
[TR]
[TD]Irn-Bru Revolution[/TD]
[TD]Pleasure Beach, Blackpool[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1979[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]Jubilee Odyssey[/TD]
[TD]Fantasy Island[/TD]
[TD]Steel[/TD]
[TD]Inverted[/TD]
[TD]Operating[/TD]
[TD="align: right"]2002[/TD]
[TD="align: right"]63[/TD]
[/TR]
[TR]
[TD]Jungle Coaster[/TD]
[TD]Legoland Windsor[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2004[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]Knightmare[/TD]
[TD]Camelot Theme Park[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]43.5[/TD]
[/TR]
[TR]
[TD]Kumali[/TD]
[TD]Flamingo Land Theme Park & Zoo[/TD]
[TD]Steel[/TD]
[TD]Inverted[/TD]
[TD]Operating[/TD]
[TD="align: right"]2006[/TD]
[TD="align: right"]54.9[/TD]
[/TR]
[TR]
[TD]Magic Mouse[/TD]
[TD]Brean Leisure Park[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]29.1[/TD]
[/TR]
[TR]
[TD]Megafobia[/TD]
[TD]Oakwood Theme Park[/TD]
[TD]Wood[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1996[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD]Millennium Roller Coaster[/TD]
[TD]Fantasy Island[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1999[/TD]
[TD="align: right"]55.9[/TD]
[/TR]
[TR]
[TD]Nemesis[/TD]
[TD]Alton Towers[/TD]
[TD]Steel[/TD]
[TD]Inverted[/TD]
[TD]Operating[/TD]
[TD="align: right"]1994[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Nemesis Inferno[/TD]
[TD]Thorpe Park[/TD]
[TD]Steel[/TD]
[TD]Inverted[/TD]
[TD]Operating[/TD]
[TD="align: right"]2003[/TD]
[TD="align: right"]47.8[/TD]
[/TR]
[TR]
[TD]New Roller Coaster[/TD]
[TD]New MetroLand[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1988[/TD]
[TD="align: right"]26.8[/TD]
[/TR]
[TR]
[TD]Oblivion[/TD]
[TD]Alton Towers[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1998[/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TD]Pepsi Max Big One[/TD]
[TD]Pleasure Beach, Blackpool[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1994[/TD]
[TD="align: right"]74[/TD]
[/TR]
[TR]
[TD]Rage[/TD]
[TD]Adventure Island[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]43.5[/TD]
[/TR]
[TR]
[TD]Rat[/TD]
[TD]Loudoun Castle[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2005[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]Rattlesnake[/TD]
[TD]Chessington World of Adventures[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1998[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]Rhino Coaster[/TD]
[TD]West Midlands Safari Park[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1992[/TD]
[TD="align: right"]28.5[/TD]
[/TR]
[TR]
[TD]Rita - Queen of Speed[/TD]
[TD]Alton Towers[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2005[/TD]
[TD="align: right"]61.1[/TD]
[/TR]
[TR]
[TD]Roller Coaster[/TD]
[TD]Great Yarmouth Pleasure Beach[/TD]
[TD]Wood[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1932[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]Roller Coaster[/TD]
[TD]Pleasure Beach, Blackpool[/TD]
[TD]Wood[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1933[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]Roller Coaster[/TD]
[TD]Wicksteed Park[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]Shockwave[/TD]
[TD]Drayton Manor Park[/TD]
[TD]Steel[/TD]
[TD]Stand Up[/TD]
[TD]Operating[/TD]
[TD="align: right"]1994[/TD]
[TD="align: right"]53[/TD]
[/TR]
[TR]
[TD]Speed: No Limits[/TD]
[TD]Oakwood Theme Park[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2006[/TD]
[TD="align: right"]59[/TD]
[/TR]
[TR]
[TD]Stealth[/TD]
[TD]Thorpe Park[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2006[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]Tornado[/TD]
[TD]M&Ds Scotland's Theme Park[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1998[/TD]
[TD="align: right"]44.7[/TD]
[/TR]
[TR]
[TD]Tsunami[/TD]
[TD]M&Ds Scotland's Theme Park[/TD]
[TD]Steel[/TD]
[TD]Inverted[/TD]
[TD]Operating[/TD]
[TD="align: right"]2004[/TD]
[TD="align: right"]38[/TD]
[/TR]
[TR]
[TD]Twist and Shout[/TD]
[TD]Loudoun Castle[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2003[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD]Twister[/TD]
[TD]Lightwater Valley[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2001[/TD]
[TD="align: right"]29.1[/TD]
[/TR]
[TR]
[TD]Ultimate[/TD]
[TD]Lightwater Valley[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1991[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Vampire[/TD]
[TD]Chessington World of Adventures[/TD]
[TD]Steel[/TD]
[TD]Suspended[/TD]
[TD]Operating[/TD]
[TD="align: right"]2002[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]Velocity[/TD]
[TD]Flamingo Land Theme Park & Zoo[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2005[/TD]
[TD="align: right"]54[/TD]
[/TR]
[TR]
[TD]Wall's Twister Ride[/TD]
[TD]West Midlands Safari Park[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1998[/TD]
[TD="align: right"]29.1[/TD]
[/TR]
[TR]
[TD]Whirlwind[/TD]
[TD]Camelot Theme Park[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2003[/TD]
[TD="align: right"]37.3[/TD]
[/TR]
[TR]
[TD]Wild Mouse[/TD]
[TD]Flamingo Land Theme Park & Zoo[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1997[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]Wipeout[/TD]
[TD]Pleasurewood Hills[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD]X:\ No Way Out[/TD]
[TD]Thorpe Park[/TD]
[TD]Steel[/TD]
[TD]Sit Down[/TD]
[TD]Operating[/TD]
[TD="align: right"]1996[/TD]
[TD="align: right"]27.7[/TD]
[/TR]
</tbody>[/TABLE]

 

[gmhumphr]

I'm going to skip your enumerated questions, because there isn't a definitive answer for the right way to approach a pivot table. It comes down to more of a style question. Instead, I'll talk about how I think about pivot tables when I create them.

From the problem you are presented with (or the question you are trying to answer), work from the bottom up:

First of all, you know that you have a global limitation. No matter what information is contained, you only care about TYPE = STEEL. This is a global/background filter, a requirement to subset the data. That field goes into the FILTER field. For that matter, you have a limitation of DESIGN = SIT DOWN. That could easily be placed in the FILTER field. Filtering eliminates items that don't meet the filter. Frequently, you will be posed with an explicit question, and when you deliver the answer, it leads to more questions. I generally default to putting grouping elements into rows/columns so you can see them still (and be able to answer the next question that hasn't been asked).

Then understand how you want to aggregate data. The question says you want to know the overall average speed. Speed is the data point that goes in ∑ Values field, follow by a left click and changing "value field settings" to find average. This says that when you find multiple items of data that have some common attributes, what action do you want to do in order to summarize or aggregate that information. When it is a number, you usually want to count the number of instances, or sum the instances together, or (in this case) take the average of the values. These are all mathematical functions that are acting upon all instances of a data point (SPEED) to return a single piece of information on several underlying data points.

From there, you can easily add things to rows/columns as needed. This will group the information. Its performing the action that the phrase "for each amusement parks, what is the overall average speed" is asking for. Fields placed in rows/columns will GROUP the contents of the column together. In short, it looks at the list, and generates a list off of it where each item exists once (thus each amusement park is represented one time) and the aggregation function is applied to all instances that meet it. If there are 6 data points (coasters) in a park, the park name will show up one time on the left, and the 6 data points will be aggregated together in the values field (average is taken on the 6 points).

The row/column feature is a place to play around to tell the story the way you want to. You could layer all elements in the rows (hard to compare), or play with columns and row interplay to show how things intersect and keep the table tighter. This is where the fun comes in!

 

[sheetspread]

(Don't look here until after you've followed gmhumphr's suggestion):

 

[bencar]

I

First of all, you know that you have a global limitation. No matter what information is contained, you only care about TYPE = STEEL. This is a global/background filter, a requirement to subset the data. That field goes into the FILTER field. For that matter, you have a limitation of DESIGN = SIT DOWN. That could easily be placed in the FILTER field. Filtering eliminates items that don't meet the filter. .

Hi gmhumphr,

Thanks for your help. I can understand why DESIGN = SIT DOWN would also be in the filter along with TYPE = STEEL. But can you explain what you said, "Filtering eliminates items that don't meet the filter"? Thats confusing.

 

[sheetspread]

Filtering hides the rows you choose.

 

[gmhumphr]

Think of this hypothetical:

You have the dataset above, but there is one more roller coaster on the list. This one is different from the others in that its TYPE = TITANIUM. If you put TYPE in the FILTER and selected only TITANIUM from the list, the pivot table would ignore every other coaster on the list, since it is the one and only coaster that meets the criteria of TITANIUM. As far as the pivot table is concerned after that filter is put in place, you have 1 and only 1 record to display. There is no more data, and nothing to summarize/aggregate. If you threw other fields in ROWS/COLUMNS, it would only show the details of that one entry.

Now contrast that with the filter you are putting in place. There are MANY records for coasters that meet the TYPE = STEEL. All of those will be included in the pivot table, and will be summarized and aggregated upon.

 

[bencar]

Think of this hypothetical:

You have the dataset above, but there is one more roller coaster on the list. This one is different from the others in that its TYPE = TITANIUM. If you put TYPE in the FILTER and selected only TITANIUM from the list, the pivot table would ignore every other coaster on the list, since it is the one and only coaster that meets the criteria of TITANIUM. As far as the pivot table is concerned after that filter is put in place, you have 1 and only 1 record to display. There is no more data, and nothing to summarize/aggregate. If you threw other fields in ROWS/COLUMNS, it would only show the details of that one entry.

Now contrast that with the filter you are putting in place. There are MANY records for coasters that meet the TYPE = STEEL. All of those will be included in the pivot table, and will be summarized and aggregated upon.

Hi, gmhumphr,

There is no rollarcoaster with type = titanium in the data. Are we looking at the same thing?

 

[gmhumphr]

Hi, gmhumphr,

There is no rollarcoaster with type = titanium in the data. Are we looking at the same thing?

That's why I suggested a hypothetical. Imagine if the same data set you shared had one more line at the bottom, and it was for a TITANIUM roller coaster.

 

[bencar]

That's why I suggested a hypothetical. Imagine if the same data set you shared had one more line at the bottom, and it was for a TITANIUM roller coaster.

Ahh I see. Thanks man. Could you please answer this question for me?
https://www.mrexcel.com/forum/excel-questions/1049860-keyboard-shortcut-return-top-sheet.html