Complete the DatabaseTotals worksheet using the appropriate database functions using the CarDatabase named range, use the MSRP as the field in your calculations, and only calculate vehicles that meet all the following criteria: Vehicle year is newer than 2015 Transmission type is Automatic Highway MPG is greater than 25
AutoSave OFF BE SU Hakes_Excel_Ch06_PS1_Automobiles Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments HV AL V V + Show Detail Connections Properties Edit Links Y Clear Reapply Advanced Stocks Sort Refresh All Geography Filter From From New Database HTML Text Query Text to Columns Consolidate Flash Fill Remove Data Duplicates Validation What If Analysis Group Ungroup Subtotal Hide Detail C2 fx B D E F G H 1 J K L M N O P Q R S T U 1 Make Model Year Transmission Type Number of Doors Vehicle Size Vehicle Style highway MPG city mpg MSRP 2 3 4 5 6 7 Make Model Year Transmission Type Number of Doors 8 Lincoln MKS 2016 AUTOMATIC 9 Aston Martin DBS 2012 AUTOMATIC 10 Toyota Camry Solara 2008 AUTOMATIC 11 Ford Mustang SVT Cobra 1998 MANUAL 12 Chevrolet Tahoe 2015 AUTOMATIC 13 Chevrolet Cobalt 2009 MANUAL 14 Lamborghini Huracan 2016 AUTOMATED_MANUAL 15 HUMMER H3 2010 AUTOMATIC 16 Toyota Sienna 2015 AUTOMATIC 17 Maserati Coupe 2004 MANUAL 18 Chevrolet C/K 1500 Series 1997 MANUAL 19 Suzuki Aerio 2005 MANUAL 20 Ford Freestar 2005 AUTOMATIC 21 Chevrolet Trail Blazer 2008 AUTOMATIC 22 Scion xD 2013 AUTOMATIC 23 Toyota Highlander 2015 AUTOMATIC 24 Volkswagen Jetta SportWagen 2013 AUTOMATIC 25 Ford Transit Connect 2017 AUTOMATIC 26 Lexus GS 430 2006 AUTOMATIC 27 Audi A3 2015 AUTOMATED MANUAL 28 Volkswagen Beetle Convertible 2015 AUTOMATED_MANUAL 29 Ferrari California T 2015 AUTOMATED_MANUAL 30 Volkswagen Passat 2016 AUTOMATIC 31 Subaru WRX 2016 AUTOMATIC 32 Dodge Charger 2015 AUTOMATIC 33 Acura Integra 2000 MANUAL 34 FIAT 500L 2016 MANUAL 35 Buick Verano 2016 AUTOMATIC 36 Suzuki X-90 1998 MANUAL 37 38 Data from "Car Features and MSRP compiled by Cooper Union, https://www.kaggle.com/Cooper Union/cardataset 39 33 Vehicle Size 4 Large 2 Midsize 2 Midsize 2 Compact 4 Large 2 Compact 2 Compact 4 Midsize 4 Large 2 Compact 2 Large 4 Compact 4 Midsize 4 Midsize 4 Compact 4 Midsize 4 Compact 4 Compact 4 Midsize 2 Compact 2 Compact 2 Compact 4 Midsize 4 Compact 4 Large 2 Compact 4 Compact 4 Midsize 2 Compact Vehicle Style Sedan Convertible Coupe Convertible 4dr SUV Coupe Coupe 4dr SUV Passenger Minivan Coupe Regular Cab Pickup Wagon Passenger Minivan 4dr SUV 4dr Hatchback 4dr SUV Wagon Passenger Minivan Sedan Convertible Convertible Convertible highway MPG city mpg MSRP 26 17 $ 39,010.00 18 12 $290,861.00 31 22 $ 21,010.00 24 16 $ 4,059.00 22 22 16 $ 49,300.00 35 25 $ 15,660.00 21 14 $238,500.00 18 14 $ 38,365.00 25 18 $ 28,700.00 15 10 $ 81,013.00 18 14 $ 2,992.00 28 22 $ 15,449.00 21 16 $ 32,755.00 20 14 $ 33.945,00 33 27 $ 16,545.00 25 19 $ 36,590.00 31 24 $ 26,195.00 27 19 $ 25,590.00 23 16 $ 51,375.00 32 23 $ 47,050.00 29 23 $ 32,595,00 23 16 $198,973.00 38 25 $ 23,975.00 24 18 $ 31,595.00 25 15 $ 47,995.00 28 22 $ 7,398.00 33 25 $ 19,495.00 32 21 $ 24,475.00 26 22 $ 2,000.00 Sedan Sedan Sedan 2dr Hatchback Wagon Sedan 2dr SUV 40 41 42 43 44 AutoSample Selected Vehicles Database Totals + P 100% AutoSave OFF HESu = Hakes_Excel_Ch06_PS1_Automobiles Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments HV Connections Properties Y Clear Reapply Advanced + Show Detail Stocks Sort Refresh All Geography Filter From From New Database HTML Text Query Consolidate Text to Flash Columns Fill Remove Data Duplicates Validation Edit Links What-If Analysis Group Ungroup Subtotal Hide Detail A1 fx Make A B C D E F H K L M N o P Q R S T U V W 1 Make Model Year Transmission Type Number of Doors Vehicle Size Vehicle Style highway MPG city mpg MSRP 2 Toyota Camry Sola 2008 AUTOMATIC 2 Midsize Coupe 31 22 $ 21,010.00 3 Volkswager Passat 2016 AUTOMATIC 4 Midsize Sedan 38 25 $ 23,975.00 4 Buick Verano 2016 AUTOMATIC 4 Midsize Sedan 32 21 $ 24,475.00 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 AutoSample Selected Vehicles Database Totals + Average: 5045.266667 Count: 40 Sum: 75679 6 100% AutoSave OFF Evo = Hakes_Excel_Ch06_PS1_Automobiles Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Lo Connections Properties V El E E E- 21 17 Y clear I Sort += Show Detail Stocks Filter From From New Database HTML Text Query Refresh All Geography Consolidate Reapply Advanced Text to Columns Flash Fill Edit Links Remove Data Duplicates Validation What-If Analysis Group Ungroup Subtotal Hide Detail B8 X Vfx B D E F G H T J K L M N 0 P 0 R S T U V W X Model Year Transmission Type Number of Doors Vehicle Size Vehicle Style highway MPG city mpg MSRP 1 Make 2 3 4 5 Field 6 7 Sum 8 Average 9 Count 10 Max 11 Min 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 AutoSample Selected Vehicles Database Totals + Edit 100% AutoSave OFF BE SU Hakes_Excel_Ch06_PS1_Automobiles Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments HV AL V V + Show Detail Connections Properties Edit Links Y Clear Reapply Advanced Stocks Sort Refresh All Geography Filter From From New Database HTML Text Query Text to Columns Consolidate Flash Fill Remove Data Duplicates Validation What If Analysis Group Ungroup Subtotal Hide Detail C2 fx B D E F G H 1 J K L M N O P Q R S T U 1 Make Model Year Transmission Type Number of Doors Vehicle Size Vehicle Style highway MPG city mpg MSRP 2 3 4 5 6 7 Make Model Year Transmission Type Number of Doors 8 Lincoln MKS 2016 AUTOMATIC 9 Aston Martin DBS 2012 AUTOMATIC 10 Toyota Camry Solara 2008 AUTOMATIC 11 Ford Mustang SVT Cobra 1998 MANUAL 12 Chevrolet Tahoe 2015 AUTOMATIC 13 Chevrolet Cobalt 2009 MANUAL 14 Lamborghini Huracan 2016 AUTOMATED_MANUAL 15 HUMMER H3 2010 AUTOMATIC 16 Toyota Sienna 2015 AUTOMATIC 17 Maserati Coupe 2004 MANUAL 18 Chevrolet C/K 1500 Series 1997 MANUAL 19 Suzuki Aerio 2005 MANUAL 20 Ford Freestar 2005 AUTOMATIC 21 Chevrolet Trail Blazer 2008 AUTOMATIC 22 Scion xD 2013 AUTOMATIC 23 Toyota Highlander 2015 AUTOMATIC 24 Volkswagen Jetta SportWagen 2013 AUTOMATIC 25 Ford Transit Connect 2017 AUTOMATIC 26 Lexus GS 430 2006 AUTOMATIC 27 Audi A3 2015 AUTOMATED MANUAL 28 Volkswagen Beetle Convertible 2015 AUTOMATED_MANUAL 29 Ferrari California T 2015 AUTOMATED_MANUAL 30 Volkswagen Passat 2016 AUTOMATIC 31 Subaru WRX 2016 AUTOMATIC 32 Dodge Charger 2015 AUTOMATIC 33 Acura Integra 2000 MANUAL 34 FIAT 500L 2016 MANUAL 35 Buick Verano 2016 AUTOMATIC 36 Suzuki X-90 1998 MANUAL 37 38 Data from "Car Features and MSRP compiled by Cooper Union, https://www.kaggle.com/Cooper Union/cardataset 39 33 Vehicle Size 4 Large 2 Midsize 2 Midsize 2 Compact 4 Large 2 Compact 2 Compact 4 Midsize 4 Large 2 Compact 2 Large 4 Compact 4 Midsize 4 Midsize 4 Compact 4 Midsize 4 Compact 4 Compact 4 Midsize 2 Compact 2 Compact 2 Compact 4 Midsize 4 Compact 4 Large 2 Compact 4 Compact 4 Midsize 2 Compact Vehicle Style Sedan Convertible Coupe Convertible 4dr SUV Coupe Coupe 4dr SUV Passenger Minivan Coupe Regular Cab Pickup Wagon Passenger Minivan 4dr SUV 4dr Hatchback 4dr SUV Wagon Passenger Minivan Sedan Convertible Convertible Convertible highway MPG city mpg MSRP 26 17 $ 39,010.00 18 12 $290,861.00 31 22 $ 21,010.00 24 16 $ 4,059.00 22 22 16 $ 49,300.00 35 25 $ 15,660.00 21 14 $238,500.00 18 14 $ 38,365.00 25 18 $ 28,700.00 15 10 $ 81,013.00 18 14 $ 2,992.00 28 22 $ 15,449.00 21 16 $ 32,755.00 20 14 $ 33.945,00 33 27 $ 16,545.00 25 19 $ 36,590.00 31 24 $ 26,195.00 27 19 $ 25,590.00 23 16 $ 51,375.00 32 23 $ 47,050.00 29 23 $ 32,595,00 23 16 $198,973.00 38 25 $ 23,975.00 24 18 $ 31,595.00 25 15 $ 47,995.00 28 22 $ 7,398.00 33 25 $ 19,495.00 32 21 $ 24,475.00 26 22 $ 2,000.00 Sedan Sedan Sedan 2dr Hatchback Wagon Sedan 2dr SUV 40 41 42 43 44 AutoSample Selected Vehicles Database Totals + P 100% AutoSave OFF HESu = Hakes_Excel_Ch06_PS1_Automobiles Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments HV Connections Properties Y Clear Reapply Advanced + Show Detail Stocks Sort Refresh All Geography Filter From From New Database HTML Text Query Consolidate Text to Flash Columns Fill Remove Data Duplicates Validation Edit Links What-If Analysis Group Ungroup Subtotal Hide Detail A1 fx Make A B C D E F H K L M N o P Q R S T U V W 1 Make Model Year Transmission Type Number of Doors Vehicle Size Vehicle Style highway MPG city mpg MSRP 2 Toyota Camry Sola 2008 AUTOMATIC 2 Midsize Coupe 31 22 $ 21,010.00 3 Volkswager Passat 2016 AUTOMATIC 4 Midsize Sedan 38 25 $ 23,975.00 4 Buick Verano 2016 AUTOMATIC 4 Midsize Sedan 32 21 $ 24,475.00 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 AutoSample Selected Vehicles Database Totals + Average: 5045.266667 Count: 40 Sum: 75679 6 100% AutoSave OFF Evo = Hakes_Excel_Ch06_PS1_Automobiles Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Lo Connections Properties V El E E E- 21 17 Y clear I Sort += Show Detail Stocks Filter From From New Database HTML Text Query Refresh All Geography Consolidate Reapply Advanced Text to Columns Flash Fill Edit Links Remove Data Duplicates Validation What-If Analysis Group Ungroup Subtotal Hide Detail B8 X Vfx B D E F G H T J K L M N 0 P 0 R S T U V W X Model Year Transmission Type Number of Doors Vehicle Size Vehicle Style highway MPG city mpg MSRP 1 Make 2 3 4 5 Field 6 7 Sum 8 Average 9 Count 10 Max 11 Min 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 AutoSample Selected Vehicles Database Totals + Edit 100%
