M

#### marq

these numbers by creating a text that reads 1-100, 101-200, 201-30

etc. This will greatly reduce the number of rows of data and mak

pivot table more effeciently.

6 1-100

234 201-300

188 101-200

44 1-100

1123 1101-120

M

these numbers by creating a text that reads 1-100, 101-200, 201-30

etc. This will greatly reduce the number of rows of data and mak

pivot table more effeciently.

6 1-100

234 201-300

188 101-200

44 1-100

1123 1101-120

Ad

S

=(ROUNDDOWN(A1/100,0)*100)&" - "&((ROUNDDOWN(A1/100,0)+1)*100)

assuming your data is in COL A

R

Why not leave your data as it is, and retain the accuracy, but in the PT

Group the data in bands of 100

M

smaller pre-defined groupings.

Have not found a way to accomplish in PT

P

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 letter-grade categories. A histogram table presents the letter-grade boundaries and the number of scores between the lowest bound and the current bound. The single most-frequent 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

E

=if(A1<101,"0-100",if(A1<201,"101-200",if(A1<301,"201-300","301-9999999999")))

I have a column of random numbers. In a second column I want to group

these numbers by creating a text that reads 1-100, 101-200, 201-300

etc. This will greatly reduce the number of rows of data and make

pivot table more effeciently.

6 1-100

234 201-300

188 101-200

44 1-100

1123 1101-1200

--

marq

=(ROUNDDOWN(A1/100,0)*100)&" - "&((ROUNDDOWN(A1/100,0)+1)*100)

assuming your data is in COL A

"marq" wrote://office.microsoft.com/en-us/excel/HP100215691033.aspx[/url]

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 letter-grade categories. A histogram table presents the letter-grade boundaries and the number of scores between the lowest bound and the current bound. The single most-frequent 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

Ad

J

(ROUNDDOWN((A1-1)/100,0)*100)+1&" - "&((ROUNDDOWN((A1-1)/100,0)+1)*100)

This would probably bucket the outliers between intervals (i.e. 100, 300)

**Want to reply to this thread or ask your own question?**

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.