How to convert Excel values ​​to buckets?

Question

I have a dataset in Excel, and in one column there is an estimate (number of weeks)

I want the Excel formula to put it in

• Small
• Medium
• Big

where, if the value is 0 - 10, then put Small. If the value is 10-20, put it on Medium, etc.,.

if there is any elegant way to do this except that all nested statements are grouped together?

Answer 1

Maybe not what you were looking for at all, but what about using conditional Excel formatting functions

EDIT: Alternatively, you can create a vba function that acts as a formula that will do the scheduling 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 

Answer 2

The right tool for this is to create a range with your limits and corresponding names. Then you can use the vlookup() function with the 4th parameter set to True to create a range search.

Note: my computer uses ; as a delimiter, yours can use,.
Customize the formula to suit your regional settings.

Answer 3

Another way to create this would be to use conditional expressions if ... which means that you are referencing a cell that has a value, and depending on that value it will give you a bucket, for example small .

For example, =if(b2>30,"large",if(b2>20,"medium",if(b2>=10,"small",if(b2<10,"tiny",""))))

So, if cell b2 had a value of 12 , then it will return the word small .

Hope this is what you are looking for.

Answer 4

If all you have to do is count , how many values ​​fall into each category, then this is a classic statistic question and can be very elegantly resolved using a “histogram”.

In Excel, you use a data analysis add-in (if you do not already have one, see the link below). Once you understand the histograms, you can divide your data into buckets - the so-called "bins" - very quickly, easily set up your bins and automatically map out the data.

These are three simple steps: 1) Put your data in one column 2) Create a column for your bins (10, 20, 30, etc.). 3) Choose "Data" → "Data Analysis" → "Histogram" and follow the instructions for choosing a range of data and bins (you can put the results in a new worksheet and display the results in the same menu)

http://office.microsoft.com/en-us/excel-help/create-a-histogram-HP001098364.aspx

Answer 5

A good way to create buckets is with the LOOKUP () function.

This example contains cell A1 — the number of days. The second parameter is a list of values. The third parameter is a 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 "})

Answer 6

You are looking for the LOOKUP function. See the next page for more information:

Data buckets (in range)

Answer 7

Here is a solution that:

• Independently
• Does not require VBA
• Unlimited in the same way as IF regarding maximum bucket values
• It does not require exact values, as LOOKUP does

 =INDEX({"Small","Medium","Large"},LARGE(IF([INPUT_VALUE]>{0,11,21},{1,2,3}),1)) 

Replace [INPUT_VALUE] with the appropriate cell link and remember to press Ctrl + Shift + Enter , as this is an array formula.

Each of the array constants can be expanded to arbitrarily long; if the formula does not exceed Excel a maximum of 8,192 characters. The first constant should contain the return values, the second should contain the ordered threshold values, and the third should just be ascending integers.

Answer 8

Perhaps this may help you:

 =IF(N6<10,"0-10",IF(N6<20,"10-20",IF(N6<30,"20-30",IF(N6<40,"30-40",IF(N6<50,"40-50"))))) 

Just replace the values ​​and text with small, medium and large.

Answer 9

If conditions are the best way to do this. If you want the account to use a bucket pivot table. This is the easiest way, and if conditions can go more than 5-6 buckets.

Answer 10

I prefer to label buckets using a numerical formula. If the bucket size is 10, then this is indicated by buckets 0,1,2, ...

 =INT(A1/10) 

If you put bucket size 10 in a separate cell, you can easily change it.

If cell B1 contains a bucket (0,1,2, ...), and column 6 contains the names Low, Medium, High, then this formula converts the bucket into a name:

 =INDIRECT(ADDRESS(1+B1,6)) 

Alternatively, this means the buckets with the lowest value in the set, i.e. 0,10,20, ...

 =10*INT(A1/10) 

or it marks them with a range of 0-10,10-20,20-30, ...

 =10*INT(A1/10) & "-" & (10*INT(A1/10)+10) 

Answer 11

I use this trick for the same data storage. Instead of the text result, you will get a number. Here is an example for four buckets. Suppose you have data in the range A1:A100 . Put this formula in B1:

 =MAX(ROUNDUP(PERCENTRANK($A$1:$A$100,A1) *4,0),1) 

Fill out the formula all over column B and you're done. The formula divides the range into 4 equal segments and returns the block number into which cell A1 is a part. The first bucket contains the lowest 25% of the values.

Adjust the number of buckets according to your desire:

 =MAX(ROUNDUP(PERCENTRANK([Range],[OneCellOfTheRangeToTest]) *[NumberOfBuckets],0),1) 

The number of observations in each bucket will be equal to or almost equal. For example, if you have 100 observations, and you want to divide them into 3 segments (as in your example), then these segments will contain 33, 33, 34 observations. So almost equal. You do not need to worry about this - the formula will work for you.