Mon, 20/10/2008 - 7:24pm — jethro

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.

## Comments

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