split data into ranges

[mmandel]

hey,

i have a list of numerical values, like:
1
2
2
4
5
6
8
10
12
14
etc

how can i split them into ranges and count the items in the ranges:
like:
1-5: 5
6-10: 3
11:15: 2

naturally, the actual table is way bigger so something as automatic as possible is needed.

thanks!
Mait

[khamilton]

try =countifs(A:A,">=1",A:A,"<=5")

[DonkeyOte]

If the ranges are consistent you can push your data into a Pivot Table, set the number field as both Row Label and Data Field (set to COUNT)... you can then in turn Group the Row field by Interval of 5 starting from 1

see attached (2007 format) for proof of concept.

If the intervals are not consistent then you could use COUNTIFS as outlined already by khamilton or think about using a helper column adjacent to the numbers to allocate each number to a group and use that helper column in your PT as Row Label.

[mmandel]

hey,

thanks for the tips.
the pivot thing wont work, excel says "cannot group that selection".
any ideas on what should i do here?

i managed to use the countifs for now, but since the number of ranges is high
(at the moment ~1000, should be much more), it's not the ideal scenario.

my data constists of ~10000 entries, numerical values ranging from 1.0 to 400
i want to plot the distribution on a graph.
maybe dividing them into ranges is not the best solution?

Mait

[mmandel]

hey,

ok, got the pivot table grouping to work now.
just copied the data into a clean sheet ...

one question though:
when pivot table says "1-5" and "5-10" then i guess it means
1>x=>5 and 5>x=>10, right? no overlapping of numbers?

Mait

[sdsame123asd]

If the ranges are consistent you can push your data into a Pivot Table, set the number field as both Row Label and Data Field (set to COUNT)... you can then in turn Group the Row field by Interval of 5 starting from 1

see attached (2007 format) for proof of concept.

If the intervals are not consistent then you could use COUNTIFS as outlined already by khamilton or think about using a helper column adjacent to the numbers to allocate each number to a group and use that helper column in your PT as Row Label.

How did you do that? I downloaded the file and the file works, but when I try to do it on my own pivot table it adds a new field.

[vijaythe1]

"Khamilton"
Needed this today...............This Works.....................Thanks..