Excel formula to sort into Bins

[lpellica]

What's the easiest formula to check to see which bin the value of a field fits into? For instance, if I want to add a column that classifies one column whose values could be potentially be one of 100 or so numerical values into 8 numerical categories. The categories would be 0 to 1, 2 to 10, 11 to 25, 26 to 40, 41 to 55, 56 to 70, 71 to 85, 86 to 110, and the appropriate category would populate another column.

Is it some combination of a formula and a lookup table, or should you do it with a formula alone?

 

[shg]

Have a look at the FREQUENCY function.

 

[lpellica]

I know how to use the FREQUENCY function to come up with a separate small table to identify how many total records fall into each category.

What I'd like to do is add a field for each record in the big data table identifying which category that record falls into, based on another field in each record. Something like... IF(AND(A2<=40,A2>25),40,?)

 

[shg]

Ah.

=LOOKUP(A1, {-9E+307,0,2,11,26,41,56,71,86,111}, {"other","0 to 1","2 to 10","11 to 25","26 to 40","41 to 55","56 to 70","71 to 85","86 to 110","other"})

 

[lpellica]

Can you help me understand what this part of the formula is for?

-9E+307

 

[lpellica]

By the way, it does work just fine, thank you!

 

[shg]

BIIIG negative number, to snag the values less than 0.

You're welcome, good luck.