Excel | Conditional formulae exercise | Footballer Card Penalties - Nested IF

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Software ==> Excel  (151 exercises)
Version ==> Excel 2010 and later
Topic ==> Conditional formulae  (17 exercises)
Level ==> Average difficulty
Subject ==> Excel training
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Open the workbook in the folder shown above.

Create a nested =IF formula in cell E2 to show the action to take based on the following:

  • If the number of yellow cards is greater than or equal to 30, then the fine is 10% of their salary;
  • If the number of yellow cards is greater than or equal to 10, then the fine is 2% of their salary;
  • If the number of yellow cards is any less than 10 then insert the text No Action in the cell.

You might like to first create some range names for the variables that you will need to use in the =IF (or you may use absolute cell refs if you prefer)

Footballers card penalties worksheet without nested =IF

The variables in column H have had range names created.

Copy your nested =IF formula down to column to calculate the action to take for all the other players.

Check that the nested =IF is working by changing each variable and then checking that the answer updates correctly.  Also check that if you change the number of yellow cards for a player, then the =IF correctly adjusts the % fine used.  Change the number of yellow cards for a player to less than 10 and check that the message appears in the cell.

If you would like an extra challenge for this exercise, read on!

A better way to display the fines would be to have the text Fine of appear before the calculated amount.  This can be added to your nested =IF formula.

Think of the above problem like this.  To display the wording Fine of before the answer to a formula in cell D7, you would create this formula:

="Fine of " & D7

Save your workbook with the new name Footballers Card Penalties with Nested IF.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.
This page has 1 thread Add post
05 May 17 at 12:50

Hello, it says in the article that "the answer to the exercise will be included and explained if you attend the Wise Owl course listed below!", however there isn't any course listed. Could you please advise?

Thank you!!

05 May 17 at 15:04

Many thanks for bringing this to our attention!  This exercise is for an older version of Excel, in which we no longer train (hence why no courses are listed).  I've made a change to our system so that it lists out the possible equivalent courses for Excel 2016, at the time of writing the version in which we run our courses. 

As it happens, the best course to attend for this topic is our two-day Advanced Excel course.  Sadly, at the moment we only run classroom training courses in the UK.

07 May 17 at 15:32

Thank you Andy for your fast reply!