The IF function is an example of a formula with valuesthat match conditions. This formula means that if a specifiedcondition or logical test is satisfied, the first action (if true) isperformed; otherwise, the second action (if false) is taken.
Locate the IF function by visiting the
Let’s try an example of the IF function, as follows:
- Open the IF-Function.xlsx workbook.
- There are two methods you can use to enter the IFfunction in a formula:
- Using the typing method (which is the one I prefer) or
- Using the Logical book from theFunction Library group.
- In cell C2, we will construct a formula that will display
Within budgetif the amount spent inthe first month is less than or equal to the budget total in F2. If itis greater, the text will display Overbudget in the cell. The formula will be typed as follows: =IF(B2<=$F$2,”Within Budget”,”OverBudget”).
- Click to select cell C2. Type an
= sign followed by IF in cell C2.
- Type a ( after the
- The autocomplete option will offer you help on how to construct theformula you are using. The process can be seen in the followingscreenshot:
- The logical_test part of theformula appears bold. This means that this part of theformula must occur first. As we are working out whether the first monthwas within budget, we need to use cell B2 in our formula totest the result against the budget total in cell F2. Therefore, thefirst part of the formula should reflect this, as illustrated inthe following screenshot:
- The second part of the formula is to enter what must happen if thecondition is met (true). Before we type this part ofthe formula, we need to place a comma after the F2 cell reference toindicate that we are moving on to the next phase of the formula. Afterthe comma, we will type “
Within Budget”, as illustrated in thefollowing screenshot. Note that you need to enter any text in a formulawith double inverted commas, before and after the text. Failure to do sowill result in a formula error:
- The third part of the formula is to enter what will happen if thecondition is not met (false). Before we type thisformula, we need to place a comma after the words
“WithinBudget“ to separate the second and third part ofthe formula. After the comma, we will type “OverBudget“ and endwith) to indicate that the formula is complete.
- Press Enter on the keyboard to place the formula into thecell and preview the result.
- If we use AutoFill to drag (copy)the formula to cells C3:C7, we will notice that all the cells reflect“OverBudget“. This isincorrect. We need to make cell F2 absolute (it has tobe fixed so that cells C3:C7 use the same formula as in C2). Refer tothe section (Understanding relative versus absolute) onabsolute cell references if you are stuck, but the easiest way is todouble-click on cell C3 and investigate why the formula is not workingcorrectly, and then go back to C2 and edit the formula.
- Edit the formula in cell C2 so that when copying the formula tocells C3:C7, the correct absolute cell reference will be used.
- Double-click on cell C2 to editthe formula.
- Highlight (select) the F2 cell reference. Press the F4function key on the keyboard to make cell F2 absolute.
- Press Enter on the keyboard to update the formula.
AutoFill cell C2 to cells C3:C7. The formulais working!
Let’s look at a more complicated IF function, as follows:
- Open the SafestSolutions.xlsx workbook.
- Type an = sign incell D5 (the answer cell) to start the formula.
- Type the function you wish to use—in this case, the IFfunction.
- Type a bracket to start formulating your arguments. Remember theIF function has three parts to it:
- We will structure our formula according to the information given. Inthis example, we are working out the bonus amount each salespersonshould receive if their SalesMade figure is greaterthan their PredictedSales figure. If their
Sales Madefigure is greater,then they will receive £500 plus 7% of the differencebetween Predicted Salesand Sales Made. If the salesperson’sSales Made figureis not higher than the Predicted Salesfigure, theywill receive only £500. Ask yourself thefollowing questions:
Therefore, the formula will look like this (please note thefollowing formula is expanded for viewing … you do not put spaces inbetween formula arguments!):
- Finish the formula to read as follows:
=IF(C5>B5,500+(C5-B5)*7%,500), asillustrated in the following screenshot:
- Close the bracket to end your formula, and pressEnter.
- Use your AutoFill handle todrag the formula to the rest of your cells.
Take time to experiment with the following comparison operators tocompare two values and produce the logical value of true or false—theIF function is a very powerful function! Have a look at thefollowing table:
|>=||greater than or equal to|
|NOT =>||not greater than or equalto|
|<=||less than or equal to|
|<>||not equal to|
|(OR(…)||where 1 or up to 30 conditions can bemet|
|(AND(…)||where 1 to 30 conditions must bemet|
Here is an example of comparison operators used in a function:
Text values can also be used with the IFfunction. We will use the same example as previously, but adapt itslightly…If our S
So … enter the following into cell D5