Technology & Engineering
IF Function Excel
The IF function in Excel is a logical function that allows users to perform different calculations based on specified conditions. It evaluates a given condition and returns one value if the condition is true and another value if the condition is false. This function is commonly used for decision-making and data analysis in spreadsheets.
Written by Perlego with AI-assistance
Related key terms
1 of 5
3 Key excerpts on "IF Function Excel"
eBook - PDFIllustrated Course Guide: Microsoft® Office 365 & Excel 2016
Introductory, Spiral bound Version
- Beskeen/Cram/Duffy/Friedrichsen/Reding, Elizabeth Reding, Lynn Wermers(Authors)
- 2016(Publication Date)
- Cengage Learning EMEA(Publisher)
A logical formula makes calculations based on criteria that you create, called stated conditions . For example, you can build a formula to calculate bonuses based on a person’s performance rating. If a person is rated a 5 (the stated condition) on a scale of 1 to 5, with 5 being the highest rating, he or she receives an additional 10% of his or her salary as a bonus; otherwise, there is no bonus. A condition that can be answered with a true or false response is called a logical test . The IF function has three parts, separated by commas: a condition or logical test, an action to take if the logical test or condition is true, and an action to take if the logical test or condi -tion is false. Another way of expressing this is: IF(test_cond,do_this,else_this). Translated into an Excel IF function, the formula to calculate bonuses might look like this: IF(Rating=5,Salary*0.10,0). In other words, if the rating equals 5, multiply the salary by 0.10 (the decimal equivalent of 10%), then place the result in the selected cell; if the rating does not equal 5, place a 0 in the cell. When entering the logical test portion of an IF statement, you typically use some combination of the comparison operators listed in TABLE 5-3 . CASE Mary asks you to use an IF function to calculate the number of places available for each experience in April, and to display “None” if no places are available. 1. Click cell F7 , on the Formulas tab click the Logical button in the Function Library group, then click IF The Function Arguments dialog box opens. You want the function to do the following: If the project capacity is greater than the number of places reserved, calculate the number of places that are available (capacity minus number reserved), and place the result in cell F7; otherwise, place the text “None” in the cell.
- Bernard Liengme(Author)
- 2002(Publication Date)
- Newnes(Publisher)
Figure 5.1 5 The Decision Functions Objectives Upon completion of this chapter you will be able to: ! construct a condition using the comparison operators =, >=, >, <= , < and <>; ! construct a formula using the IF function; ! use the logical functions AND, OR and NOT; ! use the table look up functions VLOOKUP, HLOOKUP, INDEX and MATCH; ! use the conditional functions COUNTIF and SUMIF; ! name a table and a column using I nsert|N ame|D efine; ! copy and protect a worksheet. Decision Functions The functions introduced in this chapter are useful when making decisions. They include the IF function, the logical functions AND, OR and NOT which enable one to make compound tests, and functions such as VLOOKUP, INDEX and MATCH that look up values from tables in the worksheet. The IF function is used when you want a formula to return different values depending on the value of a condition. As a simple example, suppose A2 contains the percentage increase in sales and you wish to have the word ‘Good’ or ‘Poor’ in B2 depending on whether the increase is greater than or equal to 20%. The formula =IF(A2>=20%, Good, Poor) will achieve this. The figure below shows the syntax for a formula using the IF function. 82 A Guide to Excel 2002 for Business and Management The objects that Microsoft Excel calls comparison operators are generally called relational operators in computer languages. Excel recognizes this fact; a search in Help for relational operators brings up the c o m p a r i s o n o p e r a t o r s information . A condition has the form: Expression-1 Comparison Operator Expression-2 Expression-1 and Expression-2 are any valid Excel expressions composed of cell references, constants and functions. Examples of conditions are: A1>10, A1*2>=50, and A1–B1<>2*C1. Essentially, an expression is a formula without the equal sign. Thus to test if cell A3 has a value of 5 the condition is A3=5.
- Lynn Wermers(Author)
- 2019(Publication Date)
- Cengage Learning EMEA(Publisher)
The function is complete, and the result, MET, appears in cell I3, as shown in FIGURE 3-3 . 5. Drag the fill handle to copy the formula in cell I3 into the range I4:I12 Compare your results with FIGURE 3-4 . Most offices met their target increase but four offices did not. 6. Save the workbook Build a Logical Formula with the IF Function You can build a formula in a worksheet using a logical function that returns a different value depending on whether the given condition is true or false. An IF function is a logical function that assigns a value to a cell based on a logical test. A logical formula makes calculations based on criteria that you create, called stated conditions . For example, you can build a formula to calculate bonuses based on a person’s performance rating, where the stated condition is 5. If a person is rated a 5 on a scale of 1 to 5, he or she receives an additional 10% of his or her salary as a bonus; otherwise, there is no bonus. The IF function has three parts, including the logical test , which is the first part of the function. This test is a condition that can be answered with a true or false response. If the logical test is true, then the second part of the function is applied; if it is false, then the third part of the function is applied. When entering the logical test portion of an IF statement, you often use some combination of the comparison operators listed in TABLE 3-1 . CASE Ellie asks you to calculate whether each office met or missed its revenue target for the year. Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
Index pages curate the most relevant extracts from our library of academic textbooks. They’ve been created using an in-house natural language model (NLM), each adding context and meaning to key research topics.
