Writing IF Statements in Excel

The IF Statement in Excel is a good tool, but is quite often little understood by Excel users.  The IF statement tests a value and then says whether it is true or false and then completes an action set by the user.  An example could be, is the number in Cell A1 bigger than the number in Cell B1?

The IF statement has three parts, first is the test of the item (as above, is the number in A1 larger than the number in B1?), second is what value do you want if the answer is true (you could display “yes”), and finally what value do you want if the answer is false (you could display “no”).

Writing an IF Statement

image1So, lets write an IF statement for the first row as per the picture.  In the formula you need seperate each part (or argument) by a comma.  So clicking into cell C2 we would write:

=IF(A1>B1,"Yes","No")

Then click enter.  Cell C2 should display “No” because the answer is false.  If we remove the cell references and use the values it becomes easier to understand.  So the above formula would now read:

IF(10 is greater than 4, then display “Yes”, If 10 is not greater than 4 then display “No”).  Whenever you want text displayed as your value for either true or false you must enter between quotes.  But if you want to display numbers then quotes are not required.  For example, =IF(A1>B1,1,2).  You can have almost anything as your value for either true or false.  But what if you don’t wish to display anything for one of the values, lets say the false value.  Well you simply use two quotes with nothing in between, like so:

=IF(A1>B1,"Yes","")

The downside with IF statements is that you cannot test a third item, unless that is you nest the IF statements into one formula.  You can then test multiple criteria, with nested IF statements, but in one long formula.

Leave a Reply

Your email address will not be published. Required fields are marked *