0

i have over 100k numbers - these will be anywhere from 0 to 2000

currently they are in a single column in excel, and what im trying to do is group them so excel for example counts 40 as being within range 0 - 99

is this possible - if so how?

so the data is

59
356
708
108
116
100
116
433
536
454
595
549
85
101
100
116
116
147
180
169
138
216
85
285
332
332
301
153
337
637
306
153
154
113
1681
1834
1599
1524
1614
1703
174
75
161
217
142
171
228
264
421
284
143
318
116
135
113
106

and what i want back is

0 - 99    10
100 - 199  23

etc

1
  • 1
    could you mock up what you are expecting for an output? a simple countifs would do it: =COUNTIFS(A:A,">=0",A:A,"<=99") Oct 12 '16 at 18:13
1

Place the upper and lower limits to you "buckets" in columns B and C. Then in D1 enter Scott's formula:

=COUNTIFS(A:A,">=" & B1,A:A,"<=" & C1)

and copy down:

enter image description here

1

Excel has a built-in toolkit which includes the ability to create a Histogram AKA "frequency distribution".

You'll need to enable the Analysis Toolpak in the Add-ins section of the Options menu. From there you create the "bins" of numbers for which you wish to find frequencies. The whole process is detailed by Microsoft here.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.