1 / 16

Introduction to the Excel ‘IF’ Function

Introduction to the Excel ‘IF’ Function. What is the ‘IF’ Function?. An Excel worksheet tool which enables the user to apply conditional tests on values and formulae. The calculation is based on a condition that is either TRUE or FALSE.

ssteve
Download Presentation

Introduction to 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. Introduction to the Excel ‘IF’ Function

  2. What is the ‘IF’ Function? An Excel worksheet tool which enables the user to apply conditional tests on values and formulae. The calculation is based on a condition that is eitherTRUEorFALSE. The syntax,ie the structure, of anIFformula has3 arguments(elements), described on the next 3 slides.

  3. The 3 Arguments (Elements) 1 Logical Test–Any value or expression which canbe deemedTRUEorFALSE. For example,B6=200is a logical expression. If the value is cellB6in an Excel worksheet is equal to200the expressionB6=200is, therefore,TRUE.Otherwise the expression would beFALSE. The formula required to display this expression is shown below. =IF(B6=200, “TRUE”, “FALSE”)

  4. The 3 Arguments (Elements) (cont) 2Value if true– Is the value true? Is further action necessary? If nothing is entered in the formula,TRUEis displayed. For example, if the argument in the text string is“Within Budget”and theLogical Testevaluates toTRUE, then theIFfunction displays the text“Within Budget”. The formula required to display this expression is shown below. Assume a “Within Budget” figure of £1,000 and cellC10displays£975. =IF(C10<=1000, “Within Budget”, “Over Budget”)

  5. The 3 Arguments (Elements) (cont) 3 Value if false– What action is to be taken if the value isFALSE? If nothing is entered in the formula,FALSEis displayed. For example, if the argument in the test string is“Over Budget”,and the Logical Test argument evaluates toFALSE, then theIFfunction displays the text“Over Budget”. The formula required to display this expression is the similar to the previous one, but cellD10displays an over budget figure of£1,125. =IF(D10<=1000, “Within Budget”, “Over Budget”)

  6. The IFfunction is therefore used when working with Excel worksheets when it is necessary to determinewhich of 2 valuesto use when making calculations. TheIFfunction checks a condition that must be eithertrueor false. If the condition istrue, the function returns (displays) one value. If the condition isfalse, the function returns another value.

  7. PRACTICAL EXAMPLE (A) • Open the Excel fileExams.xls. The formula below determines whether a candidate has passed or failed an exam. • In Cell D4, key-in the formula: • =IF(C4>=50,“Pass”,“Fail”) • Press the RETURN key. The result is shown on the next slide.

  8. PRACTICAL EXAMPLE (A) (cont) The formula=IF(C4>=50,“Pass”,“Fail”)returns the wordFailin Cell D4. • Position the cursor in this corner and it changes to a black cross. • Hold down the mouse button and drag the cursor downwards to cellD22. All the cells in column D now display eitherPassor Fail(see next slide).

  9. PRACTICAL EXAMPLE (A) (cont)

  10. PRACTICAL EXAMPLE (B) • Open the Excel fileBudget.xls.

  11. PRACTICAL EXAMPLE (B) (cont) • In cellB24, key-in the following formula, then press ENTER. • =IF(B22<7000,” “,”Over Budget”) In this example, the double quotes with a blank space between them ensures that if the Total Payments figure for January in cellB22isless than£7,000,nothingwill appear in cellB24, ie payments are within budget. However, if Total Payments for February to June are over budget, this will be indicated by an“Over Budget”warning text in the appropriate cells.

  12. PRACTICAL EXAMPLE (B) (cont) • Click on cellB24, which contains the formula (but it is not displayed) and drag the black cross to the right, to cellG24.

  13. PRACTICAL EXAMPLE (B) (cont) It is now clear that Payments for the months of March, April and June are over budget.

  14. PRACTICAL EXAMPLE (C) This example comparespredictedexpenses againstactualexpenses for a period of 3 months. • Open the Excel fileBudget2.xls. • In cell C24, key- in the formula • =IF(C22>B22,”Over Budget”,”OK”)

  15. PRACTICAL EXAMPLE (C) (cont) AnOver Budgetstatement appears in cellC24.

  16. PRACTICAL EXAMPLE (C) (cont) Do the same for the months of February and March and the worksheet looks like this.

More Related