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

Answer:

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

Related posts:

Categorize values into multiple columns (excel formulas)

Categorize values into multiple columns using vba in excel

Categorize data entry values (vba)

Excel: Using lookup to categorize numbers

Select 6 unique and random numbers from 1 to 49 (Lottery) in excel