IF Function
The IF function is commonly used to classify data into different groups. In it’s simplest form, you are able to classify data into 2 different groups. For example, you have a set of sales data showing the details of products sold in different countries. You wish to classify the products by those sold in sold in Singapore and those that are sold out of US. You can set up a logical test to find out whether the country of sale is in US or not in US. If it is in US, then type US in an empty column next to the country. If not, place the description “non-US” in the column. The logical test is meant to return the result as TRUE or FALSE.
Setting Up the IF formula
Assuming that the country of sales is in column A and you wish to put the new classification in column E, enter the IF formula in column E. For row 2 (assuming that the first row is the header), enter the following IF formula:
=IF(A2=”US”,”US”,”non US”)
The next step is to copy the formula down to the rest of the cells in column E. With the IF formula, you will be able to separate the US sales from the non-US sales. What if you wish to separate the non-US sales further? You can put another IF function into the first IF formula, in the portion where it is currently showing “non-US”. Excel will work on the remaining non-US sales data after the US data has been identified. In this case, the US data will not be touched. Assuming that the next category is to identify sales to Japan, you can use the IF formula IF(A2=”JPY”,”JPY”,”Others”). The nested IF formula shoud be as follows:
=IF(A2=”US”,”US”,
IF(A2=”JPY”,”JPY”,”Others”)
Notice that the “non-US” portion of the IF function has been replaced by the new IF(A2=”JPY”,”JPY”,”Others”). If you have more categories, you can continue to add in more IFs in the FALSE portion
=IF(logical test, value if true,
value if false)
In Excel 2003 and below, you can set up up to 7 IF formula in one cell. If you are using Excel 2007 and above, you can put up to 64 IFs. I wou’dn’t use so many IF in one formula. If there are more than 5 IFs, I would consider using the VLOOKUP formula instead of this IF function.
