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 IF statement is a very useful formula and is probably the most used formula, possibly just eclipsing the VLOOKUP and INDEX & MATCH formula combination. 

If Statement Excel

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 < .

IF statement example

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)

IF Statement

 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. 

Excel If Statement

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)

IF5.PNG

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.