Question

Please help me solve this probelm for "shuttle or rental." My proffesor switched uo the numbers from the probelm online and I dotm understabd it. This is using the IF statement.

OM Formulas ButoSave Scott Exp 19_Excel AppCapstone_IntroAssessment Travel. le Home Insert Formulas Page Layout Data Review View Acrobat Help Logical Define Name - eace Precedents Recently Used - Test Usein Formula Trace Dependents Name Insert unction Financial Date & Time Manager Create from Selection Remove Arrows - Function Library Defined Names Formula Al fx Autosum - X G D E Ground Transportation Round-trip Shuttle Rental Car per Day $ Rental Car Total S 50 35 (175) H Summary Trip Average Lowest Highest Total Cost с 1 Departure Date 6/17/2021 2 Return Date 6/22/2021 3 4 Lodging Base/Night $ 125 5 Total Base Lodging $ 625 6 City COL 7 1 95% 8 2 110% 9 3 125% 10 140% 11 Rental 12 Destination City City Type Car? 13 Boston 3 NO 14 Miami 2 Yes 15 Orlando 1 Yes 16 Philadelphia 3 NO 17 San Antonio 1 Yes 18 Washington DC 4 NO 19 20 21 22 23 24 25 26 Lodging Shuttle or Airfare Rental $ 367 392 299 479 218 Meals $ 375 330 285 375 285 420 29 30

Answer 1

Sheet with the FORMULAS will be as hereunder:

A B F G H 1 Departure Date 2 Return Date 44364 44369 D E Ground Transportation Round trip Shuttle Rental Car per day Rental Car Total 50 35 Summary Trip Average Lowest Highest 3 =AVERAGE(H13:H18) =MIN(H13:H18) =MAX(H13:H18) --F3*(B2-B1) 4 Lodging Base/Nigh 125 5 Total Base Lodging =(B2-B1) *B4 6 City COL 7 1 0.95 8 2 1.1 93 1.25 10 4 1.4 11 12 Destination City City Type 13 Boston 3 14 Miami 2 15 Orlando 1 16 Philadelphia 3 17 San Antonio 1 18 Washinigton DC 4 Rental car? Airfare Shuttle or Rental Lodging Meals Total Cost No 367 =IF(C13="No",$F$2,$F$3) =VLOOKUP(B13,$A$7:$B$10,2,0) *$B$5 375 ESUM(D13:G13) Yes 392 =IF(C14="No",$F$2,$F$3) =VLOOKUP(B14,$A$7:$B$10,2,0) *$B$5 330 =SUM(D14:G14) Yes 299 =IF(C15="No",$F$2,$F$3) =VLOOKUP(B15,$A$7:$B$10,2,0) *$B$5 285 ESUM(D15:15) No 479 =IF(C16="No",$F$2,$F$3) =VLOOKUP(B16, $A$7:$B$10,2,0) *$B$5 375 =SUM(D16:16) Yes 218 =IF(C17="No",$F$2,$F$3) =VLOOKUP(B17,$A$7:$B$10,2,0) *$B$5 285 =SUM(D17:17) No 469 =IF(C18="No",$F$2,$F$3) =VLOOKUP(B18,$A$7:$B$10,2,0) *$B$5 420 =SUM(D18:G18)

The output will be as hereunder:

Departure Date Return Date 06/17/21 06/22/21 50 Ground Transportation Round trip Shuttle Rental Car per day Rental Car Total Summary Trip Average $1,477 Lowest $1,132 Highest $1,814 35 125 $ (175) 625 Lodging Base/Night Total Base Lodging City 1 2 COL 95% 110% 125% 140% 3 4 Destination City Boston Miami Orlando Philadelphia San Antonio Washinigton DC Shuttle or City Type Rental car? Airfare Rental 3 No 367 $ 2 Yes 392 $ 1 Yes 299 $ 3 No 479 $ 1 Yes 218 $ 4 No 469 $ Lodging Meals Total Cost 50 $ 781 $375 $ 1,573 35 $ 688 $ 330 $ 1,445 35 $ 594 $285 $ 1,213 50 $ 781 $375 $ 1,685 35 $ 594 $285 $ 1,132 50 $ 875 $420 $ 1,814