How to convert Excel values into buckets

Question

I have a set of data in Excel and in one column is a estimate  number of weeks   I want an Excel formula to bucket it into   Small Medium Large   where if the value is 0 - 10 then put it Small   If the value is 10 - 20 put it in Medium  etc        if there any elegant way of doing it besides having nested if statements all put together

User · Answer

You re looking for the LOOKUP function   please see the following page for more info   Data Buckets  in a range

User · Answer

May be not quite what you were looking for but how about using conditional formatting functionality of Excel  EDIT  As an alternate you could create a vba function that acts as a formula that will do the calulation for you  something like   Function getBucket rng As Range  As String     Dim strReturn As String      Select Case rng Value         Case 0 to 10             strReturn    Small          Case 11 To 20              strReturn    Medium          Case 21 To 30              strReturn    Large          Case 31 To 40              strReturn    Huge          Case Else              strReturn    OMG         End Select     getBucket   strReturn End Function

User · Answer

Another method to create this would be using the if conditionals   meaning you would reference a cell that has a value and depending on that value it will give you the bucket such as small   For example   if b2 gt 30  large  if b2 gt 20  medium  if b2 gt  10  small  if b2 lt 10  tiny          So if cell b2 had a value of 12  then it will return the word small   Hope this was what you re looking for

User · Answer

A nice way to create buckets is the LOOKUP   function    In this example contains cell A1 is a count of days  The vthe second parameter is a list of values  The third parameter is the list of bucket names    LOOKUP A1  0 7 14 31 90 180 360    0-6   7-13   14-30   31-89   90-179   180-359    360

User · Answer

Here is a solution which    Is self contained Does not require VBA Is not limited in the same way as IF regarding bucket maximums Does not require precise values as LOOKUP does    nbsp    INDEX   Small   Medium   Large   LARGE IF  INPUT VALUE  gt  0 11 21   1 2 3   1      nbsp   Replace  INPUT VALUE  with the appropriate cell reference and make sure to press Ctrl Shift Enter as this is an array formula   Each of the array constants can be expanded to be arbitrarily long  as long as the formula does not exceed Excel s maximum of 8 192 characters  The first constant should contain the return values  the second should contain ordered thresholds and the third should simply be ascending integers

User · Answer

I used the attached formula to categorize sales figures into within intervals of a bin range as shown the formula is    IF MOD B5  B 1  lt  gt 0   B 1  1   B5- MOD B5  B 1     B 1      B 1  1   B5- MOD B5  B 1     B 1  - B 1     Here cells are as shown in example

User · Answer

If conditions is the best way to do it  If u want the count use pivot table of buckets  It s the easiest  way and the if conditions  can go for more than 5-6  buckets too

User · Answer

If all you need to do is count how many values fall in each category  then this is a classic statistics question and can be very elegantly solved with a  histogram      In Excel  you use the Data Analysis Add-In  if you don t have it already  refer to the link below    Once you understand histograms  you can segregate your data into buckets - called  bins  - very quickly  easily adjust your bins  and automatically chart the data   It s three simple steps  1  Put your data in one column  2  Create a column for your bins  10  20  30  etc   3  Select Data --  Data Analysis --  Histogram and follow the instructions for selecting the data range and bins  you can put the results into a new worksheet and Chart the results from this same menu   http   office microsoft com en-us excel-help create-a-histogram-HP001098364 aspx

User · Answer

The right tool for that is to create a range with your limits and the corresponding names  You can then use the vlookup   function  with the 4th parameter set to Trueto create a range lookup   Note  my PC uses   as separator  yours might use    Adjust formula according to your regional settings

User · Answer

Maybe this could help you     IF N6 lt 10  0-10  IF N6 lt 20  10-20  IF N6 lt 30  20-30  IF N6 lt 40  30-40  IF N6 lt 50  40-50         Just replace the values and the text to small  medium and large

User · Answer

I prefer to label buckets with a numeric formula   If the bucket size is 10 then this labels the buckets 0 1 2       INT A1 10    If you put the bucket size 10 in a separate cell you can easily vary it   If cell B1 contains the bucket  0 1 2      and column 6 contains the names Low  Medium  High then this formula converts a bucket to a name    INDIRECT ADDRESS 1 B1 6     Alternatively  this labels the buckets with the least value in the set  i e  0 10 20       10 INT A1 10    or this labels them with the range 0-10 10-20 20-30       10 INT A1 10   amp   -   amp   10 INT A1 10  10

User · Answer

I use this trick for equal data bucketing  Instead of text result you get the number  Here is example for four buckets  Suppose you have data in A1 A100 range  Put this formula in B1    MAX ROUNDUP PERCENTRANK  A 1  A 100 A1   4 0  1    Fill down the formula all across B column and you are done  The formula divides the range into 4 equal buckets and it returns the bucket number which the cell A1 falls into  The first bucket contains the lowest 25  of values    Adjust the number of buckets according to thy wish    MAX ROUNDUP PERCENTRANK  Range   OneCellOfTheRangeToTest     NumberOfBuckets  0  1    The number of observation in each bucket will be equal or almost equal  For example if you have a 100 observations and you want to split it into 3 buckets  like in your example  then the buckets will contain 33  33  34 observations  So almost equal  You do not have to worry about that - the formula works that out for you

[excel] How to convert Excel values into buckets?

Examples related to excel

Examples related to function