Choose Language Hide Translation Bar
Community Manager Community Manager

Binning Data Using Conditional IF-THEN Statements

Learn more in our free online course:
Statistical Thinking for Industrial Problem Solving


In this video, you learn how to create an IF-THEN formula to bin continuous data using the file


This data table includes data on the incidence of measles in the United States from 1928 to 2011. In this scenario, we want to create a new variable that groups data into three time periods: before 1963, from 1963 to 1967, and after 1967.


An example of the formula we will create is in the column Vaccine. Notice that this uses a conditional IF statement, with three THEN clauses.


We’ll remove this formula and re-create it from scratch.


First, we select the Conditional function group and then select the If function.


This creates an IF statement with two THEN clauses.


We know that we will group the data into three bins, so we add a third clause.


To do this, we click the ELSE clause, and click the caret, or insert button, from the keypad twice. This inserts two new arguments into the formula.


Now we populate the formula, one line at a time.


We click the first expression box and select the variable Year from the column list.


Then, from the Comparison function group, we select a < b. We enter 1963 into the box provided. When this condition is met for Year, we want this new column to have the text value “Before.” So, we enter “Before”, including the quotation marks, as the THEN clause.


We repeat these steps for the second line. Year is the expression, the year 1967, and we enter “Transition” as the THEN clause. 


In the last row, we need to make only one change. We enter “After” as the ELSE clause.


We click Apply to test the formula. In the data table, you see that the column Vaccine is populated with the values Before, Transition, and After.


We click OK to close the formula.


As a final test to make sure we created the formula correctly, we create a distribution analysis of Year and Vaccine. As we click through the bars in the bar chart for Vaccine, we can see that the years have been grouped into the three time periods as we expected.


Article Tags