How to make bucket counts

[javanewbie22]

Dear Experts,

Note: I'm using Excel 97. That's right.

I have a huge set of data with a wide range of values.
About 800 rows. One column.

0
1
24
56
75
2
8
45678
2
98
33356
32

etc.


I'd like to make the table that you need to make the histogram.
With counts in the appropriate cells.


Bucket Count

0 a
100 b
200 c
300
400
...
1000 x
10,000 y
50,000 z


Note that the first column will be custom on my part.
And, a, b, c, here, represent COUNTs.

I've been putzing with countif, and if statements to no avail.
Excel seems to work fine with an IF statement with a single condition.

But Excel just does not want to do a compound IF. ie.
IF (CELL1 > 100 AND CELL1 <=199) THEN
Or, at least make it obvious with some blatantly clear examples.

What is the secret please?

Thanks a lot!

 

[mikerickson]

If your data is in A1:A800, have you tried formulas like

=COUNTIF(A1:A800, "<=200")-COUNTIF(A1:A800,"<100")

or, if your list of break-points is in column B

=COUNTIF(A1:A800, "<="&B3)-COUNTIF(A1:A800,"<"&B2)

 

[javanewbie22]

Thanks, that did work!

I've been meaning to thank you, but I've
been so busy and this post was on a
computer I don't use much. Sorry for the delay.

I'd also been looking at the frequency function,
but couldn't get it to work.

While I'm at it, any ideas how it would work using Frequency?