I have a distribution of numbers (population totals) across 295 zip codes. I
would like to identify the zip code total that represents the "80/20" break
(the pareto principle - the assumption that 20% of the number zip codes
contain 80% of the population of the region).

I.e., I would like to enter a function that returns the zip code total that,
above which, the zip code totals comprise 80% of the population. This will
enable me to identify the number of zip codes that comprise 80% of the
population, comparitively small, with a COUNTIF function.

Is this possible.

Thanks,

DEI

If you Population numbers are in column A and the zipcodes are in column B
then this will give you the Zipcode of the larges in your area(expand array
to suit)

=INDEX($A$1:$B$48,LARGE($A$1:$A$48,1),2)
This would find the second
=INDEX($A$1:$B$48,LARGE($A$1:$A$48,2),2)

and so on.

-John
"DEI" wrote:

I have a distribution of numbers (population totals) across 295 zip codes. I
would like to identify the zip code total that represents the "80/20" break
(the pareto principle - the assumption that 20% of the number zip codes
contain 80% of the population of the region).

I.e., I would like to enter a function that returns the zip code total that,
above which, the zip code totals comprise 80% of the population. This will
enable me to identify the number of zip codes that comprise 80% of the
population, comparitively small, with a COUNTIF function.

Is this possible.

Thanks,

DEI

DEI,

I would sort the column with the populations in descending order, then in another column, use a set
of formulas. Let's say that your population number are in column D starting in row 2. In cell E1,
enter the formula

=IF(SUM($D$2:D2)/SUM($D$2:$D$300)0.8,IF(COUNTIF($E$1:E1,"80% here")=0,"80% here",""),"")

then copy it down to match your population data column length. 80% here will appear next to the
population group that puts the rolling sum over 80%.

HTH,
Bernie
MS Excel MVP


"DEI" wrote in message
...
I have a distribution of numbers (population totals) across 295 zip codes. I
would like to identify the zip code total that represents the "80/20" break
(the pareto principle - the assumption that 20% of the number zip codes
contain 80% of the population of the region).

I.e., I would like to enter a function that returns the zip code total that,
above which, the zip code totals comprise 80% of the population. This will
enable me to identify the number of zip codes that comprise 80% of the
population, comparitively small, with a COUNTIF function.

Is this possible.

Thanks,

DEI

Thank you Bernie, but I already tried something like that to get that result.
I am working with adjacent columns of data, so I would like to return that
"80% here" value in a cell below each column.

I still do not have it, but am working with the rank function, somehow.

DEI

"Bernie Deitrick" wrote:

DEI,

I would sort the column with the populations in descending order, then in another column, use a set
of formulas. Let's say that your population number are in column D starting in row 2. In cell E1,
enter the formula

=IF(SUM($D$2:D2)/SUM($D$2:$D$300)0.8,IF(COUNTIF($E$1:E1,"80% here")=0,"80% here",""),"")

then copy it down to match your population data column length. 80% here will appear next to the
population group that puts the rolling sum over 80%.

HTH,
Bernie
MS Excel MVP


"DEI" wrote in message
...
I have a distribution of numbers (population totals) across 295 zip codes. I
would like to identify the zip code total that represents the "80/20" break
(the pareto principle - the assumption that 20% of the number zip codes
contain 80% of the population of the region).

I.e., I would like to enter a function that returns the zip code total that,
above which, the zip code totals comprise 80% of the population. This will
enable me to identify the number of zip codes that comprise 80% of the
population, comparitively small, with a COUNTIF function.

Is this possible.

Thanks,

DEI