MODULE 4 WORKSHEET ASSIGNMENT

(60 points)

You are analyzing several airline carriers to use for your company’s corporate travel. On sheet Flights you
have compiled information regarding the number of flights from Columbus to certain key cities your
employees frequently travel. On sheet Analysis you will be analyzing this information. Sheet Fares
contains data on roundtrip airfares from Columbus to the other destinations. Sheet Discounts contains
discounts each airline is offering. You will be using this workbook with multiple worksheets in it to
compare these costs and to calculate possible discounted fares.
Download the Excel start file to begin your work. Notice that this start file only includes the data shaded
in gray or data that is given to you in it. You will be inputting proper formula or functions for the
remaining data in the unshaded cells by answering the following questions using formula or functions
and cell references from this workbook. You will input your formula or functions in the proper worksheet,
NOT in this file. Remember to start all formula or functions with an equal (=) sign and to always use cell
references where possible. Only use a $ if necessary when copying formula or functions down or across.
Also remember to use the worksheet name, then a !, then the cell value wen referencing a cell that is not
in the current worksheet.

Page 1 of 3
 MODULE 4 WORKSHEET ASSIGNMENT
(60 points)

1. (0-9 pts) Write an Excel function in cell Analysis!B3, which can be copied across the row and down
the column into range B3:D6, to determine if this airline has any flights to this destination. (Display
“Available” or “Not Available”). (Hint: Use the IF() function here where the logical test references a
cell in another worksheet and always check for if a $ is needed for the numbers in any cells used
since you are copying this function down AND also check for if a $ is needed for the letters in any
cells used since you are also copying this function across.).
=IF(flights are available for this airline to destination cell reference in Flights worksheet, display
Available, display Not Available)
=IF( , “Available”, “Not Available”)
2. (0-9 pts) Write an Excel function in cell Analysis!E3, which can be copied down the column, to
determine if this airline has flights to all 3 locations. (Display “All “or “Not All”). (Hint: Use the IF()
function here where the logical test is the AND() function with three arguments and always check
for if a $ is needed for the numbers in any cells used since you are copying this function down.).
=IF(flights available to all 3 destinations, display All, display Not All)
=IF(AND( =”Available”, =”Available”, =”Available”), “All”, “Not All”)
3. (0-9 pts) Write an Excel function in cell Analysis!F3, which can be copied down the column, to
determine if this airline has flights to any of the 3 locations. (Display “At least One” or “None”).
(Hint: Use the IF() function here where the logical test is the OR() function with three arguments and
always check for if a $ is needed for the numbers in any cells used since you are copying this
function down.).
=IF(flights available to any of the 3 destinations, display At Least One, display None)
=IF(OR( =”Available”, =”Available”, =”Available”), “At Least One”, “None ”)
4. (0-9 pts) Write an Excel function in cell Analysis!G3, which can be copied down the column, to
determine if this airline has flights to at least two of the locations. (Display “At Least Two” or “Less
than Two”). (Hint: Use the IF() function here where the logical test is the COUNTIF() function using a
range and checking for “Available”. Then the result of this COUNTIF() function will be compared to
>1 to evaluate to either a TRUE or FALSE as the logical test. Remember to always check for if a $ is
needed for the numbers in any cells used since you are copying this function down.).
=IF(COUNTIF(criteria range of flights available from this worksheet for airline, criteria of Available) is
greater than 1, display At Least Two, display Less Than Two)
=IF(COUNTIF( , “Available”)>1, “At Least Two”, “Less Than Two”)
5. (0-8 pts) Your employee Martha is considering using Frequent Flyer miles to plan a vacation. She
only goes to Chicago on business so the airline must fly to Chicago. She wants the airline to also fly
to either New York or Boston so she can plan a vacation. Write an Excel function in cell Analysis!H3,
which can be copied down, to determine if this airline meets Martha’s criteria. (Display “Will Fly” or
“Will Not Fly”). (Hint: Use the IF() function here where the logical test is the AND() function with
two arguments, one of which is an embedded OR() function with two arguments. Remember to
always check for if a $ is needed for the numbers in any cells used since you are copying this
function down.).

Page 2 of 3
 MODULE 4 WORKSHEET ASSIGNMENT
(60 points)
=IF(AND(Airline fly to Chicago?, OR(Airline fly to New York?, Airline fly to Boston?)), display Will Fly,
display Will Not Fly)
=IF(AND( , OR( , )), “Will Fly”, “Will Not Fly”)
6. (0-8 pts) Write an Excel formula and/or function in cell Fares!E3, which can be copied across and
down, to calculate a proposed discounted fare for each airline/destination. America West is offering
a 15% discount on all fares, American is offering a 10% discount, Continental is offering an 8%
discount, and Delta is offering a 5% discount. (Hint: Use a nested set of IF() functions here where the
logical test is checking the airline in column A and if TRUE sets the value to the proper percent in the
Discounts worksheet. The formula must then multiply this percent discount by the airfare to get the
overall discount price and then subtract it from the airfare. Remember to always check for if a $ is
needed for the numbers in any cells used since you are copying this function down AND to check for
if a $ is needed for the letters in any cells used since you are also copying this across.).
=Airline Airfare in current worksheet – (IF(Airline in current worksheet=”America West”, discount is
15% from Discounts worksheet, IF(Airline in current worksheet=”American”, discount is 10% from
Discounts worksheet, IF(Airline in current worksheet=”Continental”, discount is 8% from Discounts
worksheet, Discount is 5% from Discounts worksheet)) * Airline Airfare in current worksheet)
= ) - (IF( =”America West”, , IF( =”American”, , IF( =”Continental”, , ))) *

7. (0-8 pts) Write an Excel formula or function in cell Flights!E3 that displays the words, “Frequently”,
“Occasionally”, “Seldom”, or “Never”, based on the following criteria. If the number of flights for the
corresponding airline is 20 or above, display “Frequently”, if the number of flights for the
corresponding airline is between 10-20, display, “Occasionally”, if the number of flights is between
1-9, display, “Seldom”, if there aren’t any flights to the corresponding location, display “Never”.
Write the formula or function so that it can be copied down the column and across the row for the
other airlines and destinations. (Hint: Use a nested set of IF() functions here where the logical test is
checking the number of flights and if TRUE sets the value to the proper text string as described
above. Make sure you go from either highest to lowest or lowest to highest for your logical test for
number of flights. Remember to always check for if a $ is needed for the numbers in any cells used
since you are copying this function down AND to check for if a $ is needed for the letters in any cells
used since you are also copying this across.).
=IF(# of flights for the corresponding Airline/City >=20, display Frequently, IF(# of flights for the
corresponding Airline/City >=10, display Occasionally, IF(# of flights for the corresponding
Airline/Coty >=1, display Seldom, display Never)))
=IF( >=20, “Frequently”, IF( >=10, “Occasionally”, IF( >=1, “Seldom”,
“Never”)))

WHAT TO SUBMIT
Input all formula or functions and make all changes in the Excel start file as stated above. Rename your Excel
file WKSHT_M4_<Lastname>.xlsx where <Lastname> is your Last name. Submit your file to Canvas by the
stated due date.

Page 3 of 3