Spy Journal 3.0 Filtering the web for you

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.