Home 
Search 
Today's Posts 
#1




Which formula to bucket range of numbers
I have a column of random numbers. In a second column I want to group these numbers by creating a text that reads 1100, 101200, 201300 etc. This will greatly reduce the number of rows of data and make pivot table more effeciently.
6 1100 234 201300 188 101200 44 1100 1123 11011200 
#2




Answer: Which formula to bucket range of numbers
To bucket range of numbers in Excel, you can use the following formula:
Formula:
Follow these steps to apply the formula:
This will bucket the range of numbers in the second column as per the specified ranges. You can now use this column to create a pivot table and analyze your data more efficiently.
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.newusers




Which formula to bucket range of numbers
Enter in B1 and copy down...
=(ROUNDDOWN(A1/100,0)*100)&"  "&((ROUNDDOWN(A1/100,0)+1)*100) assuming your data is in COL A "marq" wrote: I have a column of random numbers. In a second column I want to group these numbers by creating a text that reads 1100, 101200, 201300 etc. This will greatly reduce the number of rows of data and make pivot table more effeciently. 6 1100 234 201300 188 101200 44 1100 1123 11011200  marq 
#4
Posted to microsoft.public.excel.newusers




Which formula to bucket range of numbers
Hi
Why not leave your data as it is, and retain the accuracy, but in the PT Group the data in bands of 100  Regards Roger Govier "marq" wrote in message ... I have a column of random numbers. In a second column I want to group these numbers by creating a text that reads 1100, 101200, 201300 etc. This will greatly reduce the number of rows of data and make pivot table more effeciently. 6 1100 234 201300 188 101200 44 1100 1123 11011200  marq 
#5




Have over 30,000 lines of data and need to summarize totals into into smaller predefined groupings.
Have not found a way to accomplish in PT 
#6
Posted to microsoft.public.excel.newusers




Bucket analysis in Excel
You can use the data analysis toolpak that comes built in, but needs to be enabled to be used. See http://office.microsoft.com/enus/ex...215691033.aspx
Once you load the toolkit, the "bucket" analysis you want to do can be done with the Histogram tool. Here's a snip from the help for that tool: "The Histogram analysis tool calculates individual and cumulative frequencies for a cell range of data and data bins. This tool generates data for the number of occurrences of a value in a data set. For example, in a class of 20 students, you can determine the distribution of scores in lettergrade categories. A histogram table presents the lettergrade boundaries and the number of scores between the lowest bound and the current bound. The single mostfrequent score is the mode of the data. Histogram dialog box Input Range Enter the cell reference for the range of data that you want to analyze. Bin Range (optional) Enter the cell reference to a range that contains an optional set of boundary values that define bin ranges. These values should be in ascending order. Microsoft Office Excel counts the number of data points between the current bin number and the adjoining higher bin, if any. A number is counted in a particular bin if it is equal to or less than the bin number down to the last bin. All values below the first bin value are counted together, as are the values above the last bin value. If you omit the bin range, Excel creates a set of evenly distributed bins between the data's minimum and maximum values. ..." Setting the bin range to the bucket limits you want will sort the data as you want. Paul EggHeadCafe  .NET Developer Portal of Choice http://www.eggheadcafe.com/default.aspx?ref=ng 
#7
Posted to microsoft.public.excel.newusers




Which formula to bucket range of numbers
I made a revision based on marqs original question:
(ROUNDDOWN((A11)/100,0)*100)+1&"  "&((ROUNDDOWN((A11)/100,0)+1)*100) This would probably bucket the outliers between intervals (i.e. 100, 300) On Monday, September 8, 2008 8:04:09 PM UTC7, Sheeloo wrote: Enter in B1 and copy down... =(ROUNDDOWN(A1/100,0)*100)&"  "&((ROUNDDOWN(A1/100,0)+1)*100) assuming your data is in COL A "marq" wrote: I have a column of random numbers. In a second column I want to group these numbers by creating a text that reads 1100, 101200, 201300 etc. This will greatly reduce the number of rows of data and make pivot table more effeciently. 6 1100 234 201300 188 101200 44 1100 1123 11011200  marq 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Trying to figure out a formula for range of numbers?  Excel Worksheet Functions  
Need 3D formula for a range of numbers across several worksheets  Excel Worksheet Functions  
Subtract Range Of Numbers using a Formula  Excel Discussion (Misc queries)  
formula to select numbers from a range  Excel Worksheet Functions  
Range of numbers in a formula...PLEASE HELP!!!  Excel Worksheet Functions 