Skillsheet-Create categories using an IF function

The IF function in a spreadsheet program can be used any time you want to populate a cell, column or row based on a condition, with one outcome if the condition is true, and another if it false.

Here, we’re going to use it to categorize contracts by their value. This will work in Excel, OpenOffice and Google Sheets.

Here’s the worksheet of contracts awarded by the Department of Fisheries and Oceans (Excel 2011 for Mac shown). It’s the same data we used in the skillsheet on getting data from the web and pasting it into a spreadsheet.

Now, let’s say we wanted to categorize the contracts into two groups, those of under $50,000 and those of $50,000 or more.

No problem!

First, let’s label column E. We’ll call it “category” here, but you can call it anything you like.

In the first data row in the new column we’ll enter this formula.

=IF(D2<50000,”Less than $50,000″,”$50,000 or more”)

Here it is in the sheet:

Let’s break that down.

The IF function has three arguments (the arguments are the parameters you feed into the function to make it do its magic).

The first is the logical (IF) test that will be performed. In this case, if the value in cell D2 is less than $50,000.

The second is what happens if the test is true. In this case, the string “Less than $50,000” will be written into the cell.

The third is what happens if it is false. here, the string “$50,000 or more is written into the cell.”

When we click the result appears for the first contract.

Now, move you mouse to the bottom right hand corner of the cell in which you wrote the formula, until the cursor becomes a + sign.  Left click your mouse, hold it, and drag the formula down the column until all of the values have been put in a category.

It’ll end up looking like this.

Now that they’re categorized, you can create a pivot table (or use the Data Pilot in OpenOffice Calc) to quickly add up the value of contracts in each category.

This is just one example of using an IF function. You can use it any time you want to set the value of a cell based on the value in another and using a logical test. If you want to have more than one test, you can nest multiple if functions within each other, as in:

=IF(Test1,Value if true,IF(Test2,Value if true,Value if neither true))

You can nest as many IF functions as you like this way.

Back to main data journalism page