Frequency on two dimensions

[HuaXC]

Sorry if this appears to be a repeat - I posted the same question yesterday
afternoon but am not seeing it now, no matter how I search/browse for it.
ANyway, I want to segment a bunch of data based on two criteria, let's say
it's income and family size. Frequency function would give me the
distribution across segments on the income dimension or the family size
dimension. Is there a way to combine the two dimensions in one calculation,
i.e. tell me how many data points fall into $50k-$60k income and family size
of 2?

Appreciate your help!
XC

 

[Dave F]

Here's your post from yesterday, with a response:
http://groups.google.com/group/micr...5f271?lnk=gst&q=HuaXC&rnum=1#7bbe2aded4b5f271

 

[HuaXC]

Thanks Dave! I don't want to type in all the SUMPRODUCT for all the different
permutation of criteria.
Frequency function allows you to have excel look up in the bins and
automatically spits out statistics for each bin. Is there
a way to do that similarly on two dimensions?

XC

 

[Bernie Deitrick]

Use a Pivot table.

HTH,
Bernie
MS Excel MVP

Thanks Dave! I don't want to type in all the SUMPRODUCT for all the different
permutation of criteria.
Frequency function allows you to have excel look up in the bins and
automatically spits out statistics for each bin. Is there
a way to do that similarly on two dimensions?

XC

 

[HuaXC]

That's if the data points themselves are values in the bin set. Take income
for example, if I have the following
10k
21,250
23,455
34,444
200,000

Pivot table will show me that there's one entry for each of the above value,
but it won't show me that there are
two points in the range of 20,000 - 30,000. Or does it? If so, will you
please tell me how to do it?

I can use Frequency() for one dimension by specifying a number of bins, and
I want that same functionality for two criteria.

By now I am guessing there's no way to do that automatically.

XC

 

[PapaDos]

How do you plan to define your "bins" ?

 

[Dana DeLouis]

Pivot table will show me that there's one entry for each of the above

value,

Hi. You are looking to "Group" your data in the Pivot Table.
Right Click one of your values, say 21,250.
Select "Group and Show Detail", Then select "Group..."

Adjust your Start & End values. (Perhaps set Start =0)
For your example, set "By" to 10000.
You Pivot table should show something like 20000-29999, etc

 

[Biff]

I don't want to type in all the SUMPRODUCT for all the different

permutation of criteria.

If you setup the criteria table properly you should only have to enter a
single formula and then drag copy.

Biff

 

[Epinn]

Re: PivotTable

Check this out:-

http://www.contextures.com/xlPivot07.html#Week

Download the sample file and have fun.

Epinn

Pivot table will show me that there's one entry for each of the above
value,

Hi. You are looking to "Group" your data in the Pivot Table.
Right Click one of your values, say 21,250.
Select "Group and Show Detail", Then select "Group..."

Adjust your Start & End values. (Perhaps set Start =0)
For your example, set "By" to 10000.
You Pivot table should show something like 20000-29999, etc

 

[HuaXC]

I learned a lot through this discussion. It looks like both Pivot Table and
SUMPRODUCT will work. Thanks a lot for the support!

XC