By Gill Myers Excel tipsExcel tips: how to write an IF statement 8 Jan 2019 Working with spreadsheets can make accounting tasks much more efficient and reliable once we have learnt to use the required functionality. However, in order to achieve that we have to combine accounting knowledge with spreadsheet skills and for many of us, bringing both areas together is a real challenge. In this article we’re going to work through how to write Excel IF statements.Comparisons are frequently used in accounting. For example, we compare the total debits to the total credits in the Statement of profit or loss (SoPL) columns in an extending trial balance (ETB), and if the value of the credits is greater than the debits, we conclude that a profit has been made. IF statements can be used to do the same.The IF function allows us to make logical comparisons and then provides an answer or statement based on the result. This means that there can be two results or outcomes. Returning to our example, more income than expenses results in profit but if debits were greater than the credits then the outcome of the same comparison would be a loss instead.If we look at the SoPL columns from the ETB below, we can easily see that the value of the credit column is more than the debit column so we know that a profit has been generated.However, if we are required to write an IF statement in cell D16 to evidence that a profit has been made, then the IF statement needed is:The IF statement can be deciphered as: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 comparisonValue_if_true Enter the outcome required if the comparison is trueValue_if_false Enter the outcome required if the comparison is falseNote: 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 hereBrowse 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.