\n
The counts of the number of times each of these countries appears in the dataset are now listed.<\/p>\n
Our dataset is in alphabetical order, so we need to select the value count for the first row (in this case, \u201952\u2019 in the Afghanistan and click the \u2018sort Z-A\u2019 button (as demonstrated in the previous how to).<\/p>\n
We discover that the country that DFID staff flew to most frequently in 2011 was the United Kingdom itself, nearly 4000 times!<\/strong><\/p>\nAt first glance, this might seem counter-intuitive. Shouldn\u2019t DFID staff be flying out of the country? Well, DFID has a good number of staff working across the world, and it makes sense that they might occasionally need to come back to the UK for meetings, etc.<\/p>\n<\/div>\n<\/div>\n
\n
\n
\n
\n
<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\n<\/div>\n<\/div>\n
\n
\n
Step 3: Change what the value displays<\/h2>\n
Our second question is a little bit trickier: what percentage of flights are on British Airways?<\/p>\n
The setup for this table is similar to the last question. We clear the table, and then drag \u2018Airline\u2019 into the \u2018ROW LABELS\u2019 box. And then we drag the same label into the \u2018VALUE\u2019 box. Automatically a PivotTable will be created with the number of flights on each airline.<\/p>\n
To fully answer our question, we want to change that count into a percent. In the VALUE box, you may notice that there is a little \u2018i\u2019 button on the right of \u2018Airline\u2019 label. Click it and a new box should appear.<\/p>\n<\/div>\n<\/div>\n
\n
\n
\n
\n
<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
When it does, you\u2019ll note that we can select count, average, sum etc. We still want the count, but we want the percentage that count is for each row of the total count. To do this we need to click the options button.<\/p>\n
In the dropdown menu \u2018Show data as:\u2019 select \u2018% of total\u2019 and then select ok.<\/p>\n<\/div>\n<\/div>\n
\n
\n
\n
\n
<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
We now have a PivotTable of all airlines. If we scroll down to British Airways, we see that 32% of all DFID staff flights in 2011 were on British Airways.<\/strong><\/p>\n<\/div>\n<\/div>\n\n
\n
\n<\/div>\n<\/div>\n
\n
\n
Step 4: Change row, column and value displays<\/h2>\n
Our final question is on the tough side, if only because there are so many elements to it:\u00a0How much did the average economy class\u00a0flight between London and Dubai cost DFID staff in 2011 compared to the cost of the average business class flight?<\/p>\n<\/div>\n<\/div>\n
\n
\n
\n
\n
\n
\n
<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
\n
\n
\n
<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
To do this we start by dragging the \u2018Departure city\u2019 label to the \u2018ROW LABEL\u2019 box. This lists out all of the departure cities, but we only want one: London. By clicking the box on the table that says \u2018Row labels\u2019, we can filter it and select only London.<\/p>\n
We can then add in a destination airport, ALSO as a \u2018ROW LABEL\u2019, but this time under the \u2018pill\u2019 for \u2018Departure city\u2019. This will result in a list of all the destinations flown from London.<\/p>\n
Part of the question asks about class of service. We\u2019ll drag that into the \u2018COLUMN LABEL\u2019 box in the top-right corner. Various columns like \u2018Business\u2019, \u2018Economy\u2019 and \u2018First\u2019 now appear.<\/p>\n
The question looks at average cost. We can drag in the \u2018Cost of booking excluding VAT\u2019 label into the VALUES box. And similar to last time, we press the \u2018i\u2019 button, and select \u2018average\u2019 instead of \u2018count\u2019 and select OK.<\/p>\n<\/div>\n<\/div>\n
\n
\n
\n
\n
<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
We now have a table with all the information we need. If we scroll down to Dubai,\u00a0we see that the average business class flight in 2011 between London and Dubai\u00a0cost DFID\u00a0\u00a31458.66, while the average economy class flight cost \u00a3444.19, and while we\u2019re here, the average economy plus flight cost \u00a3889.80.<\/strong><\/p>\nOn its own that doesn\u2019t tell us too much (we would expect business class to be more expensive), but it might be helpful when comparing year on year to see how prices are changing.<\/p>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
In this how to, we will look at how to summarise that data to answer specific questions using the PivotTable utility in Excel.<\/p>\n","protected":false},"template":"","tags":[190],"acf":[],"_links":{"self":[{"href":"https:\/\/onthinktanks.org\/wp-json\/wp\/v2\/resource\/676"}],"collection":[{"href":"https:\/\/onthinktanks.org\/wp-json\/wp\/v2\/resource"}],"about":[{"href":"https:\/\/onthinktanks.org\/wp-json\/wp\/v2\/types\/resource"}],"version-history":[{"count":0,"href":"https:\/\/onthinktanks.org\/wp-json\/wp\/v2\/resource\/676\/revisions"}],"wp:attachment":[{"href":"https:\/\/onthinktanks.org\/wp-json\/wp\/v2\/media?parent=676"}],"wp:term":[{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/onthinktanks.org\/wp-json\/wp\/v2\/tags?post=676"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}