Sometimes on a spreadsheet, you need to vary the contents of a cell based on the contents of another cell. For example you may want to perform one calculation if a cell is above a certain amount but a different calculation if the cell is below that amount. One tool for doing that is the IF function. The format of this command is =IF(Logical test, value if true, value if false). Or in plain English =IF(this is true, then do this, if not do that). Let’s walk through a couple of examples.
If column A has customer names in it, column B has their balances, and column C shows their credit limit. We want column D to display the message “Over Limit” for anyone who has exceeded their credit limit. Assuming your first row is a heading, in cell D2 you could enter
=IF(B2>C2,”OVER LIMIT”,””)
This formula is saying “If the number in cell B2 is greater than the number in cell C2, display OVER LIMIT, otherwise display nothing.” Text that you want to display must be enclosed in quotation marks. The “” after the second comma is two pairs of quotation marks. Since there is nothing between them, nothing is displayed when B2 isn’t greater than C2. Since we used relative cell references you can copy the formula down the rest of the column.
Here is another good use for IF. Let’s say you want to divide each number in column A by the number next to it in column B. But some of the entries in column B are 0. If you try to divide by 0 you get an error that shows #DIV/0!. So we could use the formula
=IF(B2=0,0,A2/B2)
Now, if the cell in column B contains a zero, our formula will display a 0 (or whatever you put after the first comma) instead of #DIV/0!.
IF’s flexibility makes it very useful. You can even use other functions inside of IF, inlcuding IF itself. In our first example, if you also wanted to also flag customers who are within $100 of their limit, you could have replaced the “” with a second IF formula that displays a different message, like this =IF(B2>C2,”OVER LIMIT”,IF(C2-B2<=100,”NEAR LIMIT”,””))
That translates to “IF B2 is greater than C2, display “OVER LIMIT”, if not then, IF C2-B2 is less than or equal to 100 display “NEAR LIMIT”, otherwise display nothing.”
Note: This is an update of a post originally published Sept/2010.
