EXCEL TIP: The IF Statement made simple

One of the functions I use most often in Excel is the IF statement. This function is very powerful and to many can seem very complicated, whereas to others it is deceptively simple and its power can be underestimated.

The basic IF statement


The basic format of an IF statement is as follows:

=IF(if this is true,return this,otherwise return this)


Example:

=IF(A2>3,"Greater than three","Not greater than three")

returns the text Greater than three if the number in cell A2 is greater than 3 and Not greater than three, if it is not.

The first argument can be any expression that can be true or false, usually using =,>,<,<=,>= or <>.

Other Examples:

=IF(A3="X","Yes","No")
=IF(SUM(A2:A10)>B2,"Over Budget","Within Budget")
=IF(A2<0,0,A2)

Combining conditions


All of the above examples include only one condition, but it is possible to combine numerous conditions using the AND and OR functions.

Theses functions are formatted as follows:

=AND(Condition 1, Condition 2, Condition 3.....Condition n)
=OR(Condition 1, Condition 2, Condition 3.....Condition n)

The AND function returns TRUE if ALL of the individual conditions are true.

The OR function returns TRUE if ANY of the individual conditions are true.

They can be used in IF statements as follows:

=IF(AND(A2<=200,A2>=100),"In Range","Out if Range")
=IF(OR(A2="X",B2="X",C2="X"),"Contains X","Doesn't Contain X")

More complex decisions (Nested IF Statements)


If the decision required is more complicated, you can have IF statements within IF statements - this is called Nesting.

Example:

=IF(A2=0,"NIL",IF(A2>0,"POSITIVE","NEGATIVE"))

If A2 is 0, this will return the word NIL, however if A is not 0 the third argument is another IF statement that will return the word POSITIVE if A2 is greater than zero, otherwise it will return NEGATIVE.

And that is the IF statement. Don't forget you can still take the easy route and get your spreadsheet built for you at Spreadsheets by Email.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report.

Comments
Andrew Burnett's picture

Nested IFs

Andrew Burnett | | Permalink

A tip I have found very useful. Complicated nested IFs are almost impossible to read! However, it is possible to expand the formula bar, break the IF onto more than one line (using Alt/Enter) and indent to make a much more readable statement. The last example of the article becomes:

=IF(A2=0,"NIL",
    IF(A2>0,"POSITIVE","NEGATIVE")
 )

This one is relatively simple, but when the nesting is 3 or 4 deep it makes a real difference.

Another tip I recommend is where one of the two possible results of the IF is simple and the other more complicated, as above, always express the condition so the simple result is first. Again this makes the statement much easier to unravel when it doesn't seem to be doing quite what you expect!

 

gfeechan's picture

Great bit of additional info there Andrew

gfeechan | | Permalink

Love the tip about expanding nested IFs in the formula bar. I'll use that.

-- Glen John Feechan BA Hon. ACA

glen@feechan.co.uk

Not Just Numbers Ezine

Free Excel Pivot Table Video

Add comment
Log in or register to post comments
This blog

Popular posts from Glen Feechan's Not Just Numbers blog - The blog for those who know it's not just about the numbers. Typical content included is primarily Excel tips and other comment relevant to those responsible for finance in their business. Glen develops spreadsheets for clients all over the world via needaspreadsheet.com and helps accountancy practices to make better use of Excel through his Excellent Accountancy business.

Get The 5 Excel features that you need to know free, by subscribing to Not Just Numbers here.