Sort numbers to groups

[bullit_nl]

Hi,

I have a list with +/- 9.000 cells in 1 column filled with numbers.

Is it possible to sort this numbers in groups.

Example:

0 - 500 = 4.500
501 - 1.000 = 4.500
etc

or

0 - 500 = 50%
501 - 1.000 = 50%
etc


Thanks

 

[GlennUK]

Have a look at the Frequency function, or summarize with a PivotTable.

 

[bullit_nl]

I tried this formula:

= COUNTIF(J2:J10000;">="&0)-COUNTIF(J2:J10000;">"&500)

but the only result i get is #NAME?

 

[GlennUK]

I tried this formula:

= COUNTIF(J2:J10000;">="&0)-COUNTIF(J2:J10000;">"&500)

but the only result i get is #NAME?

Yeah well, that's neither of the things I suggested. Try a PivotTable,

Excel Workbook
	A	B	C	D
1	Data		Count of Data
2	355		Data	Total
3	578		0-499	14
4	56		500-1000	6
5	66		Grand Total	20
6	254
7	796
8	394
9	762
10	264
11	610
12	42
13	893
14	428
15	261
16	311
17	178
18	291
19	92
20	545
21	210
Sheet12

... data in row headings, and count of data in Data area ... group rows by step of 500.

 

[bullit_nl]

Hi,

I'm trying the PivotTable. But i don't get it.

I have all numbers in column A. From A2 to A10000. In A1 I typed Data.

Now i select column A en insert a PivotTable. Then i new sheet opens with in A3 row labels and in A4 to 515 my numbers.

On the right i can't select or choose anything else as data?

 

[GlennUK]

What version of Excel are you using? I will try to tell how to use the PivotTable interface, but it's different between 2003 and 2007.

 

[bullit_nl]

Hi,

I use Excel 2010.

 

[GlennUK]

Hi,

I use Excel 2010.

I don't have 2010, but I believe that 2007 is similar.

On the right i can't select or choose anything else as data?

If the Values area is blank, you choose Data again and drag to the data/values area ( I wish I'd chosen a different column name than Data ), then change the Value Field Settings to Count.

Once you have a count per number, then right-click the numbers in the Row Labels, and choose Group from the short-cut menu.

 

[bullit_nl]

Ok now i get a new column next to data, amount of data. But still not:

0-499 =
500-1000 =

What i also don't understand is why my list of almost 10.000 numbers now is a list with just +/- 500 numbers.

 

[GlennUK]

Ok now i get a new column next to data, amount of data. But still not:

0-499 =
500-1000 =

in my last post I said:
Once you have a count per number, then right-click the numbers in the Row Labels, and choose Group from the short-cut menu.

... group rows by step of 500, starting at zero, as a guess.

What i also don't understand is why my list of almost 10.000 numbers now is a list with just +/- 500 numbers.

... you are doing a count per number, so any number that appears more than once in the source data will be reduced to one row in the pivottable.