- Excel basics (1)
- Creating formulae (5)
- Formatting worksheets (9)
- Basic printing (8)
- Charts (10)
- Absolute references (9)
- Range names (13)
- Conditional formulae (17)
- Conditional formatting (7)
- Basic tables (6)
- Formatting numbers and dates (3)
- Working with dates (1)
- Data validation (6)
- Protection (1)
- Advanced IFs (6)
- Lookup functions (14)
- Advanced lookup functions (9)
- Text functions (1)
- Advanced charts (4)
- Multiple worksheets (1)
- Advanced tables (2)
- Pivot tables (3)
- Advanced pivot tables (3)
- Scenarios (2)
- Data tables (2)
- Array formulae (2)
- Building models (2)
- Masking (2)
- Cashflow calculations (1)
- Investment appraisal (1)
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.
You can learn how to do this exercise if you attend one of more of the courses listed below!
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)
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.
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.
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?
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.