[ Home ] [ Up ] [ Exercise 1 ] [ Exercise 2 ] [ Exercise 3 ] [ Exercise 4 ] [ Exercise 5 ] [ Exercise 6 ] [ Exercise 7 ] [ Exercise 8 ] [ Exercise 9 ] [ Exercise 10 ] [ Exercise 11 ] [ Exercise 12 ] [ Exercise 13 ] [ Exercise ]

Exercise 10

For this exercise you are going to have to know how to use the IF Function.  In order to learn how to do this we are going to use the example below to help us out.  

1. In the example below there is a spreadsheet displaying England players and the number of goals they have scored in different games.

2. In this example we want two messages to be displayed depending on how many goals each player scores.  These messages will depend on two IF/THEN statements.

   1.     IF the player scores more than five or more goals THEN the message is "Good".

   2.     IF the player scores less than five goals then the message is "Needs Improvement".                                                                                                                                                                  

    

3. Excel uses a special function called the IF function to solve these IF/THEN statements.  It looks something like this:

   =IF(test, value if true, value if false)

    

   1. You always need to type =IF to start the function.

   2. Test: this is the thing you are testing.  In the above example we are testing to see if the number of goals scored is equal to or greater than 5.

   3. Value if true: this is what is displayed if the test is true.  In the above example, if the test is false we want the word "Good" to be displayed..

   4. Value if false: this is what is displayed if the value is false.  In the above example if the test is false then we want the words "Needs Improvement" to be displayed.

    

4. To display these messages we are going to need to type the following IF function into cell H3 below:

   =IF(G3>=5,"Good","Needs Improvement")

    

   1. Always type =IF to start.

   2. G3>=5 is our test.  It is saying: If G3 is greater than (>) or equal to (=) 5 then the following things will happen.

   3. "Good" is what happens if the test is true.  If G3 is greater than or equal to 5 then the word "Good" will be displayed.

   4. "Needs Improvement" is what happens if the test is false.  If G3 is less than 5 then the words "Needs Improvement" are displayed.

   5. Always put quotes ("") around words to be displayed and always separate the different parts of the function with commas.

    

5. Now type the function into cell H3 below.

Joanne wants to know if the profits made for each item are good or poor.    She wants this displayed on the spreadsheet in large bold type.

1. Open your BOOKING spreadsheet.

2. Put the column heading "Profit Quality" in the column beside the "Profit" heading.

3. Enter an IF function to determine if profits are good or poor.

   1. If profits are greater than or equal to £100 then the message "Good" should be displayed.

   2. If the profits are less than £100 then the message "Poor"  should be displayed.

Joanne introduces a discount of 10% for bookings with a total charge over £150.00.  She wants you to create a spot on BOOKING spreadsheet to figure out how much a booking is with the discount.

1. Open your spreadsheet BOOKING.

2. Select the cell under the "Total Charge" cell and label it ‘LESS 10% DISCOUNT’.

3. Enter an "IF function" in the cell next to this label to calculate any discount which the customer may recieve.

   1. If the total charge is more than £150, it must calculate and display the total charge minus 10% discount.

   2. If the charge is less than £150 not, it must display the original total charge.