Occurances of items in a range.

[Shhhh]

Hello all,

I have a large field of data about 17,000 rows. What I want to do it break
it into increments of 500 and then have excel count the occurances of each
increment and in another cell total it.

Please, for purposes of your responses assume all original data is in sheet
1 Column "F"

I want the ranges to look like this:
-5000 to -4501
-4500 to -4001
-4000 to -3501
...............
0 to 500
501 to 1000
...............
etc all the way to
4500 to 5000

Is what I'm asking even possible? and if so any help would be greatly
appreciated.


Thank you,
Shhhh

 

[Leo Heuser]

Hello all,

I have a large field of data about 17,000 rows. What I want to do it break
it into increments of 500 and then have excel count the occurances of each
increment and in another cell total it.

Please, for purposes of your responses assume all original data is in
sheet 1 Column "F"

I want the ranges to look like this:
-5000 to -4501
-4500 to -4001
-4000 to -3501
..............
0 to 500
501 to 1000
..............
etc all the way to
4500 to 5000

Is what I'm asking even possible? and if so any help would be greatly
appreciated.


Thank you,
Shhhh

Hello Shhhh

Assuming integers only, here is one way to do it (if I have
understood you correctly).


1. In e.g. D1 enter -5001, in D2 enter -4501
2. Select D12 and drag down to D11 with the fill-handler
(the little square in the lower right corner of the selection)
D11 will now hold -1
3. Enter 500 in D12 and 1000 in D13.
4. Select D1213 and drag down to D21 with the fill-handler
D21 will now hold 5000
5. *Select* E1:E22 (not E21) and enter this formula:
=FREQUENCY(F1:F17000, E1:E22)
6. Commit the formula with <Shift><Ctrl><Enter> instead of
<Enter>.
7. In E23 enter =SUM(E1:E22)

E1 will now hold all numbers <=-5001, which is nil. E2 will cover the
interval -5000 to -4501, E3: -4500 to -4001 etc. E22 will hold all
numbers >5000, which is also nil.

 

[Ken Wright]

Take a look at help for the FREQUENCY function, or even look at GROUPING
within pivot tables. If you still have a problem then post back, albeit an
example of the data would probably help.

A good intro to Pivot tables is here:-

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Leo Heuser]

Hello Shhhh

Assuming integers only, here is one way to do it (if I have
understood you correctly).


1. In e.g. D1 enter -5001, in D2 enter -4501
2. Select D12 and drag down to D11 with the fill-handler
(the little square in the lower right corner of the selection)
D11 will now hold -1
3. Enter 500 in D12 and 1000 in D13.
4. Select D1213 and drag down to D21 with the fill-handler
D21 will now hold 5000
5. *Select* E1:E22 (not E21) and enter this formula:
=FREQUENCY(F1:F17000, E1:E22)
6. Commit the formula with <Shift><Ctrl><Enter> instead of
<Enter>.
7. In E23 enter =SUM(E1:E22)

E1 will now hold all numbers <=-5001, which is nil. E2 will cover the
interval -5000 to -4501, E3: -4500 to -4001 etc. E22 will hold all
numbers >5000, which is also nil.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Sorry, the formula is of course:
=FREQUENCY(F1:F17000, D121)

Leo Heuser