Exercise 1: Movies flop - IF functions

Open the file in the folder shown above.

Create an If function to calculate whether each movie was a flop or a success. Use the following
criteria:

 If the profit was less than 100,000,000 then the movie is a flop

 Otherwise the movie is a success

Copy the function down to rate each movie in the list.

I can't believe Van Helsing was a success either

Save the file with the same name and close it down.

Exercise 2: Poohsticks rating - IF functions

Open the file in the folder shown above.

Create an If function to rate the players based on the following criteria:

 If a player scores more than 15 points he has a High score

 Otherwise he must Try harder

Copy the function down to rate all the players.

Only scores more than 15 are high scores

Save the file with the same name and close it down.
Exercise 3: Poohsticks rating - Vlookup

Create a lookup table containing scores and ratings based on the following categories:

 0-4 = Pooh

 5-9 = Could do better

 10 - 14 = Doing better

 15 or more = Tiggerific

Create a VLOOKUP function to calculate a rating for each player as follows:

The function should look up the score for each player

Save the file with the same name and close it down.

Exercise 4: Widget Sales - IF functions

Create an If function to calculate a total order value based on the following criteria:

 If less than 1000 units are ordered each unit costs the normal price

 Otherwise each unit costs the discounted price

Copy the formula down and check that it works.

I don't know what Mickey needs so many widgets for

Save the file with the same name and close it down.
Exercise 5: Bob the Builder Charge - IF function

Create an If function to calculate the total for each labour item in the charge sheet based on the
following criteria:

 If the rate is Standard the row total is equal to Hours multiplied by the standard rate

 Otherwise the row total is equal to Hours multiplied by the overtime rate

Copy the formula down and make sure that it works.

Range names make this exercise much easier

Save the file with the same name and close it down.

Exercise 6: Football Targets - IF functions

Create an If function to calculate a bonus for each player based on the following criteria:

 If a players Goals Scored meets or exceeds his Goals Target he receives a bonus equal
to £1000 for each goal he has scored

 Otherwise he receives an encouraging message

Copy the formula down and check that it works.

Not that encouraging a message after all

Save the file with the same name and close it down.
Exercise 7: Widget sales - Vlookup

Fill in the unit prices table based on the following categories:

 1 - 499 = £1

 500 - 999 = £0.90

 1000 - 1499 = £0.80

 1500 or more = £0.70

Your Lookup table should begin as shown in the diagram below:

Start with the smallest amount that could be ordered

Use a Vlookup function to calculate the total value of each order.

Your formula should lookup the quantity ordered

Save the file with the same name and close it down.
Exercise 8: Car tax - Vlookup

On the Lookup Table worksheet give the table a sensible range name.

TaxTable might be a sensible name for the table

On the Main List table create Vlookup functions to work out the tax band, 12 months tax, and 6
months tax for each of the cars.

The function should look up the emissions

Save the file with the same name and close it down.

Exercise 9: Catalogue - Vlookup

Create the following two range names:

 Code - referring to H3

 Stock - referring to B4:E12

Create a lookup function in each of the yellow cells to return the details for the product code typed
into the green cell.

Typing in a different code should return the correct data

Save the file with the same name and close it down.
Exercise 10: Bob the Builder - Vlookup

Create the following range names:

 RatesTable - referring to H6:I9

 Hours - referring to D6:D16

 Rates - referring to E6:E16

Use a Vlookup function in a formula to calculate the total for each item.

It's enough to make you think about a career change

Save the file with the same name and close it down.
Exercise 11: Holidays - Vlookup

Four range names have been created in this file. Use the name box to find out what they refer to.

Click a range name to select the cells it refers to

Create a Vlookup function in each of the yellow cells to return the base price and multiplier for the
chosen destination and holiday type.

The hedonistic holiday to Thailand is the most expensive

Try typing in different destinations and holiday types to make sure the functions are working
properly.

Save the file with the same name and close it down.
Exercise 12: Teletubbies coffee - Nested If

Create a nested If function to describe the Teletubbies coffee drinking habits based on the
following criteria:

 0 cups = Tea drinker

 1-5 cups = Normal

 More than 5 cups = Caffeine fiend

Copy the function down and check that it works.

Think carefully about how to structure the If function

Try modifying the If function so that if Cups of coffee is a negative number you see an
appropriate error message.

Make sure the rest of the function still works!

Save the file with the same name and close it down.
Exercise 13: Purchase Ledger Codes - Nested If

Create a nested If function to translate the codes in the Area Code column into full descriptions
based on the following criteria:

 MS = Market Stall

 OA = Other Activities

 IE = Import\Export

Copy the function down and make sure that it works.

You should be able to do this using just two If functions

Try expanding the formula so that you see an appropriate error message if you don't type in one
of the above three codes.

Make sure the rest of the formula still works!

Save the file with the same name and close it down.
Exercise 14: Teletubbies coffee - Vlookup

Create a lookup table like the one shown in the diagram below and give it a sensible range name:

You don't need to include the titles in the range name

Create a Vlookup function to describe each Teletubby.

Po needs to take it easy

Try nesting your lookup function inside an If function so that you see an appropriate message if
the cups of coffee is less than zero.

The function should work normally for positive numbers

Save the file with the same name and close it down.