Spreadsheet using Excel (IF statement).pptx

IGCSE ICT - Spreadsheets using Microsoft Excel - IF statements

1. SpreadsheetS IF FUNCTION

2. Objectives Understand the use of IF FUNCTION in excel

3. Starter: What is a difference between a formula and a function in Excel?

4. RECAP TASK FOR PRACTICE

5. EXAMPLE 1 If the student mark iS 50 or more then the student passes otherwise they fail. HOW CAN WE IMPLEMENT THIS IN EXCEL? 5

6. =IF( LOGIC_TEST , IF TRUE , IF FALSE ) 6 IF mark >= 50 , then “PASS”, otherwise “FAIL”

7. EXAMPLE 2 If an item has a delivery charge add $10 to the price Understanding the model: The model shows electronic items and the price of each item Some items have a delivery charge - so IF the item has a ‘ Y ’ we must add the $10 to the price to calculate the total charge. 7

8. =IF( LOGIC_TEST , IF TRUE , IF FALSE ) 8 IF the delivery = “Y”, then Total Charge = Price + 10, otherwise Total Charge = Price

9. LOGICAL TESTS

10. TASK 1 In cell  C4  create an  =IF  function to calculate the commission.  Use the following to help you: If the sales in column B >= 30000, commission rate is 20% If the sales are anything less, commission rate is 1% Copy the commission rate formulae down column C to calculate commissions for all of the other reps. Change your commission rate input cells in column F to check that your  =IF  function is working correctly

11. TASK 2 – PAST PAPER QUESTION Open Task 3 questions Open the FILE diveplanner.csv to do the QUESTIONS on it.

12. Plenary