Calculating frequencies

  • Thread starter Gene Goldenfeld
  • Start date
G

Gene Goldenfeld

I'm frustrated (or maybe embarrassed) to have to ask this, but I can't
find in Help or a MS Office Pro 2000 book how to calculate the
frequency of unique values (zipcodes) listed in a single column, let
alone put the resultant list in another column (or anywhere empty).
I'd be grateful if someone would point the way. This is Excel 2000.
Thanks.

Gene
 
B

Biff

Hi!

Use an advanced filter to copy the uniques to another column then use
Countif to get the frequency.

See if you can figure it out from here. If you still need assistance just
post back and I (or someone) will be glad to give you a step-by-step.

Biff
 
G

Gary''s Student

The simplest approach to to first insure that there is a label cell on top of
the column (i.e. ZIP) and then generate a pivot table:

Data > Pivot Table and under layout drag ZIP to the row area and count of
ZIP to the data area.
 
G

Gene Goldenfeld

Biff said:
Hi!

Use an advanced filter to copy the uniques to another column then use
Countif to get the frequency.

See if you can figure it out from here. If you still need assistance
just post back and I (or someone) will be glad to give you a
step-by-step.

Biff
Click to expand...

Thanks, but it sounds like I misstated my goal. It's to create a list
of counts (by zipcode) of how many times each zipcode appears in the
original list (of 3000).

Gene
 
G

Gene Goldenfeld

Gary''s Student said:
The simplest approach to to first insure that there is a label cell
on top of the column (i.e. ZIP) and then generate a pivot table:

Data > Pivot Table and under layout drag ZIP to the row area and
count of ZIP to the data area.
Click to expand...

That's what I thought! But here's the frustration: where do you pick up
a field or something called "count" or "count of zip" to drop in the
data area? Note the clarification that I'm trying to get a count of
how many times each zipcode appears in a list (not how many unique
entries there are).

Gene
 
G

Gene Goldenfeld

I just found a workaround using Pivot Tables: Drop Zipcode in both the
row and data areas, which creates a worksheet with zipcode x frequency
in the B column. Create another column with B/A and that gets the
counts. But my gosh, isn't there a more direct and intuitive way to do
this?

Gene
 
G

Gene Goldenfeld

Ok, that works more directly. Thanks! Too bad this is not described
more explicitly in the Help text, since it seems like a common
operation.

One more question: if I wanted to extract the first three digits of the
zipcode and do counts on those, what would be the first step?

Gene
 
G

Gary''s Student

There are all kinds of people in the world. I love pivot tables. Even on a
single column, they work just fine.
 
B

Biff

Just use an additional column. Based on that screencap put the header "First
3" in E2.

Enter this formula in E3:

=SUMPRODUCT(--(LEFT(A$2:A$10,3)=LEFT(C3,3)))

Copy down as needed. Hint: double click the fill handle for quick copying.

Biff
 
G

Gene Goldenfeld

Yeah, but you really didn't answer my question: how to use a pivot
table to directly get frequencies of items in a list. There was no
second or count variable to drop into the data area of the table, thus
forcing me to do a workaround.

Gene
 
Top