1. Create a PIVOT table to answer the question.

How many records meet the following criteria: Origin State is Hawaii and the Flight is not cancelled.

2. Use Advanced Filters only to answer the question. Filter the list in place. Keep the results or no marks will be rewarded.

How many records meet the following criteria: Age is more than 40 and No of Flights per Annum is more than 30 and Type of Travel is Business Travel;

OR Flight Cancelled is Yes.

OR Airline Status is Gold.

3. Use Simple Filters to answer the following question:

How many records meet the following criteria: Arrival Delay greater 5 mins is No and Destination State is Alaska or Arizona or Arkansas or Louisiana and Flight Distance is less than 1500.

Record your answer and then clear the filter.

4. In cell AE12 use the IF function to enter long if the Flight Time in Minutes is greater than 140 otherwise the cell should be blank. Copy the function to the other cells in the column.

In cell AE1 use the appropriate function to calculate how many records have long. (format as comma style 0 decimal).

5. In cell AD12 use the IF function to enter Yes if the Departure Delay in Minutes is greater than 60 otherwise the cell should be blank. Copy the function to the other cells in the column.

In cell AD1 use the appropriate function to calculate how many records have Yes. (format as comma style 0 decimal)

4 5 Satisfaction Airline Status Age Gender Type of Travel Class Month Flight Date Airline Code Airline Name Price Year of No of Flights of Flight Sensitivity First Flight per annum with other Alrlines Origin City Shopping Amount At Airport Day of Month Eating and Drinking at Alrport QUESTION 7 8 9 10 11 12 13 6 7 8 9 2 3 4 70017 65.18863 20.25559 5 6 803240 $ 976,430.00 0 No of Flights per annum 10 Satisfaction Airline Status Age Gender Type of Travel CIAS Month YAR Flight Date Airline Code Price Year of Sensitivity First Flight Airline Name Kaf Flight with other Airlines Origin City Shopping Amount at Airport Day of Month 4.5 1 75 3 31 50 28 41 8 9 14 7 3 7 MO MO 2 2 1 2.5 4 21 43 15 0 10 11 12 13 14 2 15 16 17 18 19 20 135 45 25 18 11 25 20 25 16 6 1 1 2 5 49 10 2 3.5 4 4 35 33 44 5 17 0 0 60 90 Blue Blue Blue Blue Silver Gold Gold Silver Blue Blue Blue Blue Platinum Silver Blue Blue Silver Blue 90 1 1 1 1 1 1 1 1 1 51 28 Male Male Terrale Male Male Ferrale Male Mala Ferral Ferrale Male Fortale Ferrale Terrale Female Male Ferrale Ferale For al Ferrale Ferrale 25 130 15 1 4 & 12 37 17 29 13 32 34 45 5 21 19 19 4 4 4 2 5 3.3 22 23 24 25 26 27 60 18 Mer 14 11-Jan-14 25-Jan-14 20-12-14 25 Feb 14 16-Jan-14 6-Mar-14 S Feb 14 21 Jan 14 19-Jan-14 19-Mar-14 4 Feb 14 15-Jan-14 S-Teb-14 17-Feb-14 9-Jan-14 4-Jan-14 2007 2006 2006 2007 2006 2010 2011 2010 2003 2005 2009 2005 2012 2010 2012 2009 2003 2011 2010 2003 2008 2005 2010 2010 2005 Business travel Business travel Personal Travel Business travel Business travel Business travel Business travel Business travel Business travel Business trave Business travel Business travel Business trys Business travel Mileactices Milcase ticket Personal Travel Perumal Trav Personal Travel Personal Travel Personal Travel Personal Trawl Personal Travel Personal Travel Personal Travel 3 2 2 1 1 3 Z 1 2 2 8 24 16 6 1 2 2 3 Business Business Business Eca ECO ECE Fest Eca ECO Era Fca Eca Eco Eco Eca ECO LCO Fea Eca ECB ECO Eca ECO Eco Eco 0 60 0 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 75 75 140 60 15 3 3 3 3 15 5 17 9 26 52 45 62 24 ErjayFlying Air Services EnjoyFlying Air Services Erjoyflying Air Services FrjayFlying Air Services ErjayFlying Air Services EnjoyFlying Air Services Erjayflying Air Services ErjayFlying Air Services ErjayFlying Air Services Enjoyflying Air Services FrjayFlying Air Services ErjayFlying Air Services EnjoyFlying Air Services Erjoyllying Air Services ErjayFlying Air Services ErjayFlying Air Services Lrjoylying Air Services FrjayFlyine Air Services ErjayFlying Air Services ErgyFlying Air Services ErjoyFlying Air Services FrjayFlying Air Services Erjayflying Air Services EnjoyFlying Air Services ErjayFlying Air Services Madison, W Madison, W Milwaukee, Madison, W MilwaukeV Madison, W Madison, W Madison, W Milwauker Madison, W Milwa MilwaukeV Milwaukee, V Madison, W Madison, W Milwaukee Milwaukee, Milwa am Madison, W Madison, W Madison, W Milwaukee Madison. W Milwaukee Milway MO MO MO MO MC MO MO MO MO MO MO MO MO MO MO MO MO MC MC MO MO MO 2 3 1 2 0 60 4 9 15 2 1 80 130 Blue 75 19 35 35 30 32 32 23 3 6C 27 5 0 8 16 19 1 1 2 3 Z 1 1 1 Male 1 2 2 1 2 1 3 2 3 1 2 64 21 Blue Blue Blue Blue Blue Blue 62 16 83 30 45 10 & 2 5 21 42 3 Mar 14 16-Feb-14 15-Jan-14 24-Jan-14 15 Jan 14 6-foar-14 29-Mar-14 Male Ferrale Female 24 15 b 29 D 0 36 47 18 165 4 5 Satisfaction Airline Status Age Gender Type of Travel Class Month Flight Date Airline Code Airline Name Price Year of No of Flights of Flight Sensitivity First Flight per annum with other Alrlines Origin City Shopping Amount At Airport Day of Month Eating and Drinking at Alrport QUESTION 7 8 9 10 11 12 13 6 7 8 9 2 3 4 70017 65.18863 20.25559 5 6 803240 $ 976,430.00 0 No of Flights per annum 10 Satisfaction Airline Status Age Gender Type of Travel CIAS Month YAR Flight Date Airline Code Price Year of Sensitivity First Flight Airline Name Kaf Flight with other Airlines Origin City Shopping Amount at Airport Day of Month 4.5 1 75 3 31 50 28 41 8 9 14 7 3 7 MO MO 2 2 1 2.5 4 21 43 15 0 10 11 12 13 14 2 15 16 17 18 19 20 135 45 25 18 11 25 20 25 16 6 1 1 2 5 49 10 2 3.5 4 4 35 33 44 5 17 0 0 60 90 Blue Blue Blue Blue Silver Gold Gold Silver Blue Blue Blue Blue Platinum Silver Blue Blue Silver Blue 90 1 1 1 1 1 1 1 1 1 51 28 Male Male Terrale Male Male Ferrale Male Mala Ferral Ferrale Male Fortale Ferrale Terrale Female Male Ferrale Ferale For al Ferrale Ferrale 25 130 15 1 4 & 12 37 17 29 13 32 34 45 5 21 19 19 4 4 4 2 5 3.3 22 23 24 25 26 27 60 18 Mer 14 11-Jan-14 25-Jan-14 20-12-14 25 Feb 14 16-Jan-14 6-Mar-14 S Feb 14 21 Jan 14 19-Jan-14 19-Mar-14 4 Feb 14 15-Jan-14 S-Teb-14 17-Feb-14 9-Jan-14 4-Jan-14 2007 2006 2006 2007 2006 2010 2011 2010 2003 2005 2009 2005 2012 2010 2012 2009 2003 2011 2010 2003 2008 2005 2010 2010 2005 Business travel Business travel Personal Travel Business travel Business travel Business travel Business travel Business travel Business travel Business trave Business travel Business travel Business trys Business travel Mileactices Milcase ticket Personal Travel Perumal Trav Personal Travel Personal Travel Personal Travel Personal Trawl Personal Travel Personal Travel Personal Travel 3 2 2 1 1 3 Z 1 2 2 8 24 16 6 1 2 2 3 Business Business Business Eca ECO ECE Fest Eca ECO Era Fca Eca Eco Eco Eca ECO LCO Fea Eca ECB ECO Eca ECO Eco Eco 0 60 0 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 75 75 140 60 15 3 3 3 3 15 5 17 9 26 52 45 62 24 ErjayFlying Air Services EnjoyFlying Air Services Erjoyflying Air Services FrjayFlying Air Services ErjayFlying Air Services EnjoyFlying Air Services Erjayflying Air Services ErjayFlying Air Services ErjayFlying Air Services Enjoyflying Air Services FrjayFlying Air Services ErjayFlying Air Services EnjoyFlying Air Services Erjoyllying Air Services ErjayFlying Air Services ErjayFlying Air Services Lrjoylying Air Services FrjayFlyine Air Services ErjayFlying Air Services ErgyFlying Air Services ErjoyFlying Air Services FrjayFlying Air Services Erjayflying Air Services EnjoyFlying Air Services ErjayFlying Air Services Madison, W Madison, W Milwaukee, Madison, W MilwaukeV Madison, W Madison, W Madison, W Milwauker Madison, W Milwa MilwaukeV Milwaukee, V Madison, W Madison, W Milwaukee Milwaukee, Milwa am Madison, W Madison, W Madison, W Milwaukee Madison. W Milwaukee Milway MO MO MO MO MC MO MO MO MO MO MO MO MO MO MO MO MO MC MC MO MO MO 2 3 1 2 0 60 4 9 15 2 1 80 130 Blue 75 19 35 35 30 32 32 23 3 6C 27 5 0 8 16 19 1 1 2 3 Z 1 1 1 Male 1 2 2 1 2 1 3 2 3 1 2 64 21 Blue Blue Blue Blue Blue Blue 62 16 83 30 45 10 & 2 5 21 42 3 Mar 14 16-Feb-14 15-Jan-14 24-Jan-14 15 Jan 14 6-foar-14 29-Mar-14 Male Ferrale Female 24 15 b 29 D 0 36 47 18 165