Worksheet 3, Task 3: 1 question (2 marks) Question Cells G4:49 Task/problem Example Excel Functions/features to you should use Calculate the living allowance David Silver joined the IF, OR, YEAR for staff: firm before 2016 and is VLOOKUP "Senior" so he is Staff are allocated a city living permitted a city living Define the name range allowance if they joined the finn allowance. He is located C15:D20 as before 2016 (column C) OR if in Melbourne (i.e., cell "City allowance" and use they hold a "Senior" position E4), so he receives a 10% this in your VLOOKUP (column D). bonus of $18.500. Cell D4 should display $18,500,- Calculate the city allowances using the rates specified in the Hugo Chevi joined the "City allowance table". Where firm in 2019 and is staff do not receive an allowance Junior" and thus is not the text "No living allowance" permitted a living should be displayed. allowance and the text "No living allowance" should be displayed. le A D E G A B F 1 2 3 First Name Last Name Date Started Position Location Salary Living allowance 4 David Silver 2014/9/2 Senior Melbourne $185,000 5 Harriette Brown 2015/8/11 Junior Singapore $120,000 6 Hugo Chevi 2019/11/11 Junior Hong Kong $120,000 7 Sally Chan 2020/6/14 Senior New York $210,000 8 Tyrion Smith 2020/4/1 Junior New York $150,000 9 Jamie Brown 2017/7/1 Senior Sydney $220,000 10 11 12 13 City allowance table 14 City City allowance 15 Hong Kong 15% 16 London 20% 17 Melbourne 10% 18 New York 20% 19 Singapore 20 Sydney 10% 21 22 20%

Worksheet 3, Task 3: 1 question (2 marks) Question Cells 1 G4:G9 Task/problem Example Excel Functions/features to you should use Calculate the living allowance David Silver joined the IF, OR, YEAR, for staff: firm before 2016 and is VLOOKUP "Senior" so he is Staff are allocated a city living permitted a city living Define the name range allowance if they joined the firm allowance. He is located C15:D20 as before 2016 (column C) OR if in Melbourne (i.e., cell "City_allowance" and use they hold a "Senior" position E4), so he receives a 10% this in your VLOOKUP (column D). bonus of $18,500. Cell D4 should display $18,500. Calculate the city allowances using the rates specified in the Hugo Chevi joined the "City_allowance table". Where firm in 2019 and is staff do not receive an allowance Junior" and thus is not the text "No living allowance" permitted a living should be displayed. allowance and the text "No living allowance" should be displayed. B C D E F G 1 2 3 First Name Last Name Date Started Position Location Salary Living allowance 4. David Silver 2014/9/2 Senior Melbourne $185,000 5 Harriette Brown 2015/8/11 Junior Singapore $120,000 6 Hugo Chevi 2019/11/11 Junior Hong Kong $120,000 7 Sally Chan 2020/6/14 Senior New York $210,000 8 Tyrion Smith 2020/4/1 Junior New York $150,000 9 Jamie Brown 2017/7/1 Senior Sydney $220,000 10 11 12 13 City allowance table 14 City City allowance 15 Column1 Column2 16 Hong Kong 0.15 17 London 0.20 18 Melbourne 0.10 19 New York 0.20 20 Singapore 0.20 21 Sydney 0.10