1 / 13

MONTEGO BAY HIGH SCHOOL INFORMATION TECHNOLOGY THE EXCEL IF FUNCTION

MONTEGO BAY HIGH SCHOOL INFORMATION TECHNOLOGY THE EXCEL IF FUNCTION. OBJECTIVES Students should be able to.. 1 Design logical test conditions Use the If Function to manipulate figures in a spreadsheet. The IF Function

vanida
Download Presentation

MONTEGO BAY HIGH SCHOOL INFORMATION TECHNOLOGY THE EXCEL IF FUNCTION

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MONTEGO BAY HIGH SCHOOL INFORMATION TECHNOLOGY THE EXCEL IF FUNCTION

  2. OBJECTIVES Students should be able to.. 1 Design logical test conditions Use the If Function to manipulate figures in a spreadsheet

  3. The IF Function The IF function is one of Excel’s most useful and most used functions. What it does, basically, is test to see whether a certain condition is true or false. If the condition is true, the function will do one thing, if the condition is false, the function will do something else.

  4. The Syntax The basic form or syntax of the function is: =IF(logic test, value if true, value if false)

  5. The Logic Test Excel's logic functions always involve a comparison between two values. The logic test can only return a true or false answer, depending on the condition tested. The Comparison Operators The comparison operators that can be used in a logic test are: Equals ( = ) Less than ( < ) Less than or equal to ( < = ) Greater than ( > ) Greater than or equal to ( > = ) Not equal to ( < > )

  6. Logical Test Cont…. Examples The logic test can be a comparison between two cell references such as: A3 > B3 Or the logic test can be a comparison between a cell reference and a fixed amount such as: C4 < = 100

  7. Performing calculations The IF function can perform different calculations depending on whether the function returns a true value or not. .

  8. Eg =IF(A4>=100,Value_if_true,Value_if_false) If The value in Cell A4 is > 100 or = to 100 (the logical test is true and Value_if_true will be displayed If the value in cell A4 is > 100 the logical test is false and value_if_false will be displayed.

  9. The example below uses different deduction rates in its calculations based on employee income. =IF(A5 < 29701, A5 * 15%, A5 * 25%) The logical test is A5<29701 Value_if_true is A5 *15% Value_if_false is A5*25% Notice 1 0f two values will be displayed depending on wither the logical test is true or false.

  10. =IF(A5 < 29701, A5 * 15%, A5 * 25%) If the value in cell A5 is less than or equal to 29701, the value in cell A5 will be multiplied by 15% (TRUE) If the value in cell A5 is greater than 29701, the value in cell A5 will be multiplied by 25% (False)

  11. CALCULATING PAYE If an applicant earns $5000.00 or less no PAYE is deducted. This base figure is called an income tax threshold. Income tax is calculated on the amount exceeding $5000.00 Therefore if an applicant’s salary is $5100, PAYE is calculated on (5100-5000) or on 100. NOT $5100.00.

  12. Calculating PAYE cont…… Build a logical test, one that will determine wither the salary is equal to or less than $5000.00 Build an expression for value_if_true, that is an expression that will calculate the PAYE if it is equal to or less than $5000.00 Build an expression for value_if_false, that is an expression that will calculate the PAYE if the salary is greater than $5000.00

  13. Displaying non numeric values using the IF function. Non numeric values are placed in open and closed quotation marks. Eg =if(A4>=50, “Blue”,”Black”) If true the word Blue will be displayed If false the word Black will be displayed

More Related