On Wednesday 18 August, 2010 at 4:39:23 am UTC+5:30, Erick McKesson wrote:
You can use an if formula. Keep the equation going as long as you want and for the last argument just make the value if false be the next range beginning and a high number for end.
=if(A1<101,"0-100",if(A1<201,"101-200",if(A1<301,"201-300","301-9999999999")))
On Monday, September 08, 2008 9:25 PM 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 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
On Monday, September 08, 2008 11:04 PM Sheelo 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:
On Tuesday, September 09, 2008 3:12 AM Roger Govier wrote:
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
On Tuesday, September 09, 2008 6:46 AM marq wrote:
Have over 30,000 lines of data and need to summarize totals into into >>>> smaller pre-defined groupings.
Have not found a way to accomplish in PT
--
marq
On Monday, January 19, 2009 11:16 PM Paul Dwyer wrote:
You can use the data analysis toolpak that comes built in, but needs to be enabled to be used. See http://office.microsoft.com/en-us/excel/HP100215691033.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 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
Submitted via EggHeadCafe - Software Developer Portal of Choice
Composite UI Pattern and RAD Development for Data Entry Applications, Part 1
http://www.eggheadcafe.com/tutorials/aspnet/a119aebe-7478-4aaa-b415-12786ec5cf90/composite-ui-pattern-and-rad-development-for-data-entry-applications-part-1.aspx
Hi Erick, This is such an easy and elegant solution. Thanks so much!!
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)