If C16 is greater than B16, then return the word Profit, if not, then return the word Loss.The brackets, commas and speech marks are all essential for the function to work and if they are omitted or used incorrectly then the formula is unlikely to be accepted or an error message will be returned. Excel will automatically add the correct basic ‘grammar’ though, if the function arguments tool is used. Click into the cell you want the IF statement in and then click on ‘fx’ just to the left of the formula bar. Then search for and/or select the IF function: The argument box for the IF function has three boxes which splits the statement up into its three logical sections: Logical_test Enter the comparison Value_if_true Enter the outcome required if the comparison is true Value_if_false Enter the outcome required if the comparison is false Note: The speech marks were automatically added to box 2 when I moved onto box 3 and will be added to the text in box 3 when I select ‘OK’ to complete the task. The IF statement, with the required basic ‘grammar’, is produced in the formula bar as the information is entered into the argument box. IF statements can be used to return any text depending on the comparison required. Are the totals of the columns in a trial balance equal – yes or no? How do the actual sales figures compare to the targets set – over target or under target? Actual production exceeded expected production so a bonus is payable this month – true or false? You should be mindful of how a question is asked though because it will affect how the statement is written and be aware that there can be more than one way of producing the same result. Therefore, it is always a good idea to check that the IF statement has returned the result you were expecting. IF statements can also be used to return calculations. For example, let’s say employees are eligible for a bonus of 2% of the sales revenue they have generated in excess of their target. We could start with a simple IF statement that returns yes or no to the question, is a bonus payable? Then we can use another IF statement to calculate the amount payable: Note: When an IF statement is comparing text or words then the spelling must be an exact match and speech marks need to be manually added* or an error message will be generated: Brackets also need to be included when a calculation has more than one operation (addition, subtraction, division etc.) because Excel works on BODMAS principles.** In this case the actual must be deduced from the target before 2% can be calculated of the difference. If the brackets are omitted then the multiplication will happen before the subtraction and the figure calculated will be wrong. A quick manual check of A Abli’s figures (the actual is more than the target and 2% of £236 is £4.72) provides the reassurance needed that the IF statements are correct and can therefore be applied to the other three employees using direct cell referencing, in other words highlighted and dragged: The IF function is one of the most popular and useful functions in Excel and has lots of practical applications to both financial and management accounting tasks. Whenever you are combining accounting knowledge and spreadsheet skills, firstly think about how you would perform the accounting task manually and then consider how you can best replicate that in Excel using formulas, functions and formatting to make the process more efficient and accurate. Read more tips on Excel here Browse the full range of AAT study support resources here * Comparing TRUE or FALSE is the only exception and doesn’t required speech marks but the spelling must still be correct. ** BODMAS is the mathematical rule that gives the order in which to do calculations that have more than one operation. Brackets (calculations within brackets always come first), Orders (powers or square roots), Division, Multiplication, Addition and lastly Subtraction.
Gill Myers is a self-employed accounts consultant. She has taught AAT qualifications since 2005 and written numerous articles and e-learning resources.