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

Here's your post from yesterday, with a response:
http://groups.google.com/group/micro...2aded4 b5f271
--
Brevity is the soul of wit.


"HuaXC" wrote:

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

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

"Dave F" wrote:

Here's your post from yesterday, with a response:
http://groups.google.com/group/micro...2aded4 b5f271
--
Brevity is the soul of wit.


"HuaXC" wrote:

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

Use a Pivot table.

HTH,
Bernie
MS Excel MVP


"HuaXC" wrote in message
...
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

"Dave F" wrote:

Here's your post from yesterday, with a response:
http://groups.google.com/group/micro...2aded4 b5f271
--
Brevity is the soul of wit.


"HuaXC" wrote:

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

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

"Bernie Deitrick" wrote:

Use a Pivot table.

HTH,
Bernie
MS Excel MVP


"HuaXC" wrote in message
...
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

"Dave F" wrote:

Here's your post from yesterday, with a response:
http://groups.google.com/group/micro...2aded4 b5f271
--
Brevity is the soul of wit.


"HuaXC" wrote:

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

How do you plan to define your "bins" ?
--
Regards,
Luc.

"Festina Lente"


"HuaXC" wrote:

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

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

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"HuaXC" wrote in message
...
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

"Bernie Deitrick" wrote:

Use a Pivot table.

HTH,
Bernie
MS Excel MVP


"HuaXC" wrote in message
...
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

"Dave F" wrote:

Here's your post from yesterday, with a response:
http://groups.google.com/group/micro...2aded4 b5f271
--
Brevity is the soul of wit.


"HuaXC" wrote:

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

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

"HuaXC" wrote in message
...
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

"Dave F" wrote:

Here's your post from yesterday, with a response:
http://groups.google.com/group/micro...2aded4 b5f271
--
Brevity is the soul of wit.


"HuaXC" wrote:

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

PivotTable

Check this out:-

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

Download the sample file and have fun.

Epinn

"Dana DeLouis" wrote in message ...
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

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"HuaXC" wrote in message
...
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

"Bernie Deitrick" wrote:

Use a Pivot table.

HTH,
Bernie
MS Excel MVP


"HuaXC" wrote in message
...
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

"Dave F" wrote:

Here's your post from yesterday, with a response:
http://groups.google.com/group/micro...2aded4 b5f271
--
Brevity is the soul of wit.


"HuaXC" wrote:

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

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

XC

"HuaXC" wrote:

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