Quick Excel Tips to Boost Productivity: IF Statements

IF statements are useful for making logical comparisons between a value and what you expect. They always come with two options based on whether a result is TRUE or FALSE. With an IF statement, you instruct Excel to return one value if the statement is TRUE, and another value if it is FALSE.

IF statements take the following form: =IF(logic_test, value_if true, value_if false)

IF functions can be used to evaluate texts, errors, and values in an operation. The only exception is that texts should be wrapped in quotation marks. If you are using the texts TRUE and FALSE, however, then there’s no need for the quotes since Excel fully understands these terms.

How to Use IF Statements

Excel’s IF statements are powerful and easy to modify to fit different scenarios. Say you keep a record of your daily workout routine and would like to know how you fared in doing push ups at the end of the week. Excel’s IF functions can help you know if you have reached your target or not.

With a daily target of 100, you create an IF statement such that: =IF(E5>$H$6,”Target”,”Fail”).

You are asking Excel to return Target if you did more than 100 push ups, and Fail, if you did less.

Another useful example of IF statements is when calculating tax payments for income earners. You can set a taxable rate below which no tax deductibles are made but above which a flat rate is applied to the portion of the income above the non-taxable level.

Take the table below as an example:

The IF statement is expressed as follows: =IF(G3<200,0,(G3-200)*0.2)

This example tells us that if the income is smaller than 200, then the income tax payment due is zero. Otherwise, Excel should deduct the nontaxable base of 200 and then apply a 20% (0.2) flat rate on the excess income. The IF function gives us the corresponding values easily.

Microsoft Excel’s IF statements are very useful, and can be created to report on vendors whose payments are due, workers who have yet to reach their milestones, or tenants whose contracts are expiring.

Nested IF Statements

You can create up to seven nested IF functions in Microsoft Excel. This can be useful in grading a batch of products, for example. A nested IF statement allows Excel to do a more complex and nuanced analysis of the values in each cell.

A simple IF statement like =IF(B9>10, “Fail”, Pass) could be ideal in certain situations. But what if you want a tiered grading system where 10 is excellent, 9 is very good, 8 is good, 7 is fair, 6 is average, and 5 is fail? Nested IF statements allow you to do this with one formular. You can then replicate it across hundreds of cells to return the exact grades for each corresponding score in the Excel worksheet.

=IF(E3>9,”Excellent”,IF(E3>8,”Very Good”,IF(E3>7,”Good”,IF(E3>6,”Fair”,IF(E3>5,”Average”,IF(E3>4,”OK”,”Fail”))))))

Take note of the parenthesis. Excel will return an error if you miss one. The easiest way to ensure you have the right number of parentheses is to count the number of opened brackets; opened brackets should be equal to the number of closed brackets.