binning data in excel

By: Prof. Dr. Fazal Rehman Shamil | Last updated: February 3, 2024

Data binning is the another name of data discretization, data categorization, data bucketing, or data quantization. Data binning is a data mining methodology to simplify a column of data, by reducing the number of possible values into small groups or categories.

If you want to study data binning in details, then you can read the article mentioned below in the reference link. Otherwise, you can continue reading of data binning in excel.

Detailed lecture on what is data binning with examples

How to data binning in excel?

Just enter your data in Microsoft excel and apply the given formulas;

 

MarksTotal marksbinsBin number
2100FailBIN1
3100FailBIN1
3100FailBIN1
10100PassBIN2
22100PassBIN2
22100PassBIN2
31100PassBIN2
33100PassBIN2
34100PassBIN2
44100PassBIN2
55100PassBIN2
55100PassBIN2
65100PassBIN2
98100PassBIN2
98100PassBIN2

 

 

Formula for “Bin” column

= IF (A3<6, “Fail”, “Pass” )

= IF (A4<6, “Fail”, “Pass” )

= IF (A5<6, “Fail”, “Pass” )

= IF (A6<6, “Fail”, “Pass” )

= IF (A7<6, “Fail”, “Pass” )

= IF (A8<6, “Fail”, “Pass” )

= IF (A9<6, “Fail”, “Pass” )

= IF (A10<6, “Fail”, “Pass” )

= IF (A11<6, “Fail”, “Pass” )

= IF (A12<6, “Fail”, “Pass” )

= IF (A13<6, “Fail”, “Pass” )

= IF (A14<6, “Fail”, “Pass” )

= IF (A15<6, “Fail”, “Pass” )

= IF (A16<6, “Fail”, “Pass” )

Formula for “Bin Number” column

= IF ( C2 = “Fail”, “BIN1”, “BIN2” )

= IF ( C3 = “Fail”, “BIN1”, “BIN2” )

= IF ( C4 = “Fail”, “BIN1”, “BIN2” )

= IF ( C5 = “Fail”, “BIN1”, “BIN2” )

= IF ( C6 = “Fail”, “BIN1”, “BIN2” )

= IF ( C7 = “Fail”, “BIN1”, “BIN2” )

= IF ( C8 = “Fail”, “BIN1”, “BIN2” )

= IF ( C9 = “Fail”, “BIN1”, “BIN2” )

= IF ( C10 = “Fail”, “BIN1”, “BIN2” )

= IF ( C11 = “Fail”, “BIN1”, “BIN2” )

= IF ( C12 = “Fail”, “BIN1”, “BIN2” )

= IF ( C13 = “Fail”, “BIN1”, “BIN2” )

= IF ( C14 = “Fail”, “BIN1”, “BIN2” )

= IF ( C15 = “Fail”, “BIN1”, “BIN2” )

= IF ( C16 = “Fail”, “BIN1”, “BIN2” )

Leave a Reply