Article updated on February 23, 2009

Question: How to categorize numbers in excel? Like age into 0>5, 6>10, 11>15 and so on?

categorize_numbers.png

Answer:

categorize_numbers_1.png

In cell D1 =SUM(IF(C1>=$A$1:$A$35,1,0)) + Ctrl + Shift + Enter

In cell D2:D16, =SUM(IF((C2>=$A$1:$A$35)*(C1<$A$1:$A$35),1,0)) + Ctrl + Shift + Enter

Let us calculate the value in cell D2: IF((C2>=$A$1:$A$35)*(C1<$A$1:$A$35),1,0)  gives us two arrays {0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1}*{1,1,1,1,0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0) equals {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}

SUM(IF((C2>=$A$1:$A$35)*(C1<$A$1:$A$35),1,0)) sums {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0} = 1

Download excel sample file for this tutorial. categorize-numbers.xls (Excel 97-2003 Workbook *.xls)

SUM(number1,[number2],>)
Adds all the numbers in a range of cells

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE