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.

﻿

•

New! Comments

Have your say about what you just read! Leave me a comment in the box below.