IF Statements in Excel
The IF statement in Excel is an Excel function which checks the TRUE conditions. If the condition being tested is TRUE, the formula will return one result, if on the other hand the condition is FALSE then a different result can be displayed.
The following is an online lesson which takes you through some of the elements of this lesson on IF Statements. File at the bottom of this page. The following Excel file goes with the video.
The IF statement is a very useful formula and is probably the most used formula, possibly just eclipsing the VLOOKUP and INDEX & MATCH formula combination.
The above table shows an example of where the IF Statement can be used. The statement is being used to check if the total value of product A & B combined is greater than 400.
=IF(E2<400,"Good", "Poor")
If the value in Cell E2 is greater than 400 then the TRUE part of the equation is evoked which is to return the value HIGH. If on the other hand the value in cell E2 is less than 400 then the value LOW is returned.
To check the opposite of the above with our IF Statement, all we need to do is reverse the sign by changing the greater than symbol > to a less than symbol < .
As shown above the opposite result is shown.
Here is another example – if we wanted to test which was the greater product A or B we might do the following:
=IF(C2<D2,$C$1, $D$1)
Where C2 contains the value for Product A, D2 contains the value for Product B. The results are the headers locked in so they display the effective results.
Use IF to Check for Blank Cells
Let’s have a look at another example where the IF statement is extremely handy. The following image shows data in a table that is not displaying correctly as Actuals have not been entered or they were zero values. This has a knock on effect and the data in the table displays incorrectly. The conditional formatting is out and also the cell percentage is incorrect.
To rectify this situation we can use the IF Statement to check if that there is a value in the Actual column. This has two affects, it will clean the formula up and fix the conditional formatting.
=IF( 2="","",(E2-F2)/F2)
Notice how the formula checks the cell with the Actual and if blank displays “” blank text. Otherwise the required formula is displayed. In this case it works well. It has the added advantage of cleaning the conditional formatting Icon sets up.
The following Excel file goes with the above lesson and the video on Youtube.