## Excel Function of the Week - IF

I have decided to write a short post once a week looking at a single Excel function. This week we are going to look at the IF function. Definition from Excel Help

The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(A1>10,"Over 10","10 or less") returns "Over 10" if A1 is greater than 10, and "10 or less" if A1 is less than or equal to 10.

Syntax

IF(logical_test, value_if_true, [value_if_false])

My explanation

The IF function is best thought of as a solution to “either - or” scenarios. Here are some good examples with the syntax to use for each one.

• If you are dividing by an number ant it could be zero, the result will be a #DIV/0 error. In this case you can use an IF function to determine if the number you are dividing by is either zero or something else. =IF(B1=0,0,A1/B1)
• If you are wanting to analyse a cell for positive or negative values, eg either a value greater than zero or less than zero. =IF(A1>=0,”Positive”,”Negative”)
• If you are wanting to see if a either a cell is empty or it isn’t empty. =IF(A1=””,”Empty”,”Not Empty”)

All these are great examples and highlight the use of IF. Of course where I have used text results such as “Positive” or “Empty” you can put formulas using other functions, links to other cells or even nest another IF function.

There is a limit to the number of nested IF statements you can use. In Excel 2003 and earlier it was 7. In Excel 2007 it is 64 which is a great improvement.

## Comment viewing options ### I think limit of 64 if

I think limit of 64 if statements would promote poor formula skills.

### Hi Bhavik I would have to

Hi Bhavik
I would have to agree!
However it is a vast improvement on the previous limit of 7 - which was often too few.
See this article I wrote just the other day where I think I used 9 or 10 http://www.spyjournal.biz/node/932
Cheers
Tim

### Thanks for this post. I know

Thanks for this post. I know when teaching functions in Excel, my students always have a hard time wrapping their heads around the IF function. Thanks for taking the time to write this one.

### Thanks for the feedback

Thanks for the feedback Heather. Stay tuned for more basic functions. ### Thanks for this, sometime you

Thanks for this, sometime you forget the basics, thanks for the reminder. ### I still learn something new

I still learn something new everyday. IF function is new to me so thanks for pointing it out. I can already see where it can be used in a few of my spreadsheets, thanks so much for this.   