Median
 

Using XL 07, want to calculate median.
have worksheet of census data by zipcode that makes displays data within 25
miles of inputted zipcode. these may range from 20 to nearly 60 zipcodes.
one field is median hshld income.
once the zips w/in 25 are identified, a macro sorts the distances in
ascending order and places an "X" in the 25 mile column, several calcualtions
are made for summing or other math using <if (AK9=X, then....) and so forth

unable to determine how to calculate a median of median hshld incomes when
the number of included zips varies.

another view would be a macro: starting in row 9 and continue down and
perform median calc until AKxx no longer ="X", if that makes sense.
thanks

Median
 

Let's say your column with X's is B2:B100 and Household income is in C2:C100

=median(if(B2:B100="X",C2:C100))

Enter with CTRL SHIFT ENTER

HTH,
Barb Reinhardt

"reno" wrote:

Using XL 07, want to calculate median.
have worksheet of census data by zipcode that makes displays data within 25
miles of inputted zipcode. these may range from 20 to nearly 60 zipcodes.
one field is median hshld income.
once the zips w/in 25 are identified, a macro sorts the distances in
ascending order and places an "X" in the 25 mile column, several calcualtions
are made for summing or other math using <if (AK9=X, then....) and so forth

unable to determine how to calculate a median of median hshld incomes when
the number of included zips varies.

another view would be a macro: starting in row 9 and continue down and
perform median calc until AKxx no longer ="X", if that makes sense.
thanks

Median
 

Thx,
After several weeks of working on this to get it faster and more manageable,
I was just drawing a blank on how-to, yours is simple and elegant a real gem.
Thanks

"Barb Reinhardt" wrote:

Let's say your column with X's is B2:B100 and Household income is in C2:C100

=median(if(B2:B100="X",C2:C100))

Enter with CTRL SHIFT ENTER

HTH,
Barb Reinhardt

"reno" wrote:

Using XL 07, want to calculate median.
have worksheet of census data by zipcode that makes displays data within 25
miles of inputted zipcode. these may range from 20 to nearly 60 zipcodes.
one field is median hshld income.
once the zips w/in 25 are identified, a macro sorts the distances in
ascending order and places an "X" in the 25 mile column, several calcualtions
are made for summing or other math using <if (AK9=X, then....) and so forth

unable to determine how to calculate a median of median hshld incomes when
the number of included zips varies.

another view would be a macro: starting in row 9 and continue down and
perform median calc until AKxx no longer ="X", if that makes sense.
thanks

Median
 

Using the same scenario as described, if I wanted to calc the % in each
zipcode I keep getting circular reference. I want to total the household
within 25 miles, if distance<=25 and "X" goes in a column, then want to sum
all households with "X" and then calculate the % in each corresponding
zipcode.

any thoughts?

"Barb Reinhardt" wrote:

Let's say your column with X's is B2:B100 and Household income is in C2:C100

=median(if(B2:B100="X",C2:C100))

Enter with CTRL SHIFT ENTER

HTH,
Barb Reinhardt

"reno" wrote:

Using XL 07, want to calculate median.
have worksheet of census data by zipcode that makes displays data within 25
miles of inputted zipcode. these may range from 20 to nearly 60 zipcodes.
one field is median hshld income.
once the zips w/in 25 are identified, a macro sorts the distances in
ascending order and places an "X" in the 25 mile column, several calcualtions
are made for summing or other math using <if (AK9=X, then....) and so forth

unable to determine how to calculate a median of median hshld incomes when
the number of included zips varies.

another view would be a macro: starting in row 9 and continue down and
perform median calc until AKxx no longer ="X", if that makes sense.
thanks