binning data in excel

By Prof. Fazal Rehman Shamil
Last modified on March 3rd, 2022

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;

 

Marks Total marks bins Bin number
2 100 Fail BIN1
3 100 Fail BIN1
3 100 Fail BIN1
10 100 Pass BIN2
22 100 Pass BIN2
22 100 Pass BIN2
31 100 Pass BIN2
33 100 Pass BIN2
34 100 Pass BIN2
44 100 Pass BIN2
55 100 Pass BIN2
55 100 Pass BIN2
65 100 Pass BIN2
98 100 Pass BIN2
98 100 Pass BIN2

 

 

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” )

 

Prof.Fazal Rehman Shamil (Available for Professional Discussions)
1. Message on Facebook page for discussions,
2. Video lectures on Youtube
3. Email is only for Advertisement/business enquiries.