Creating the occurrence frequency chart in Excel

[Generic Usenet Account]

I had a need to put a series of numbers into buckets of size 5. I
created the following formula, and "dragged it down"

=A1 + IF(MOD(A1,5), 5-MOD(A1,5), 0)

I am sure that there is a better way of doing this. Can someone
suggest it?

Also, I wanted to come up with an occurrence frequency chart for my
data. For example if I have numbers 4, 9, 13, 13, 17, 19, 19, 19,
then I would like the occurrence frequency chart to plot the following
informatiuon in a 2D chart:

0-4: 1
5-9: 2
10-14:2
15-30: 3

Thanks,
Song

 

[Guest]

Hi:

This will work out the buckets for you. Paste it into the first cell and
copy down and it will give you 1,1,2,4 (Typo on your answer).

=SUMPRODUCT(--($A$3:$A$10>=5*(COUNT(ROW($A$3:A3))-1)),--($A$3:$A$10<(5*COUNT(ROW($A$3:A3)))))

You need to adjust the $A3:$A$10 to your data range.