Excel Function of the Week - IF

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

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.


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

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Bhavik's picture

I think limit of 64 if

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

jethro's picture

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

Heather's picture

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.

jethro's picture

Thanks for the feedback

Thanks for the feedback Heather. Stay tuned for more basic functions.

Zoran's picture

Thanks for this, sometime you

Thanks for this, sometime you forget the basics, thanks for the reminder.

kerosen's picture

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.Case PariuriClasamente FotbalStatistici