Answers for Excel test

The answer to the first part of the test, creating a template with various functions and formulas is online at Zoho Sheet at http://tinyurl.com/yuc72p

To see formulas and functions, click in a cell then look in the formula box near the top of the window, just as you would in Excel itself.

Note the following --
The -30 you were told to use (see A2:B7) serves a later purpose so that when you subtract Baggage Weight from Baggage Allowance, you don't get a negative number.

You need a zero in the first column of the first VLOOKUP table (A2:B7) so that when baggage is overweight between 0 and 5 kg, it is charged at the specified $4.00 per kg.

The VLOOKUP function for "Passenger Class" requires a fourth parameter, "FALSE," since you want an exact match, not merely a nearest match - e.g. =VLOOKUP(B14,$E$2:$F$6,2, FALSE)

The Overweight Amount is calculated with a simple formula subtracting Baggage Weight from Baggage Allowance - e.g. =D14-E14.

The Overweight Fee Rate is calculated with a VLOOKUP function - e.g. =VLOOKUP(F14,$A$2:$B$7,2)

The Overweight Charge is calculated with a formula that multiplies the Overweight Fee Rate by the Overweight Amount - e.g. =F14*G14

Be sure that the template is protected, with only the specified cells accessible to the user.

Having finished the template, you were toopen two worksheets (agent001.xls, agent002.xls) based on the template.

The 6-mark question required linking the Summary worksheet with data in the two Agent worksheets.

Cell G1 in the summary worksheet should contain the function =IF(F2>115, "Flight Overloaded", "")