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

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.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Generic Usenet Account" wrote:

> 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
>
>