Range Median

The Range Median function is used to calculate a median from a series of data giving a count within a range.  For the purposes of this explanation Median Household income will be used and the example data will be AGS 2004 data for Boulder County, Colorado.

The data as provided for AGS for Boulder looks like:

Var	Desc	Households	Running Total
HIXCYLT05	Income    $0 to $5,000	4233	4233
HIXCY0510	Income   $5,000 to $9,999	2991	7224
HIXCY1015	Income  $10,000 to $14,999	4277	11501
HIXCY1520	Income  $15,000 to $19,999	4364	15865
HIXCY2025	Income  $20,000 to $24,999	4756	20621
HIXCY2530	Income  $25,000 to $29,999	4991	25612
HIXCY3035	Income  $30,000 to $34,999	5483	31095
HIXCY3540	Income  $35,000 to $39,999	5109	36204
HIXCY4045	Income  $40,000 to $44,999	5180	41384
HIXCY4550	Income  $45,000 to $49,999	4709	46093
HIXCY5055	Income  $50,000 to $54,999	4784	50877
HIXCY5560	Income  $55,000 to $59,999	4701	55578
HIXCY6065	Income  $60,000 to $64,999	4720	60298
HIXCY6570	Income  $65,000 to $69,999	3637	63935
HIXCY7075	Income  $70,000 to $74,999	3943	67878
HIXCY7580	Income  $75,000 to $79,999	4110	71988
HIXCY8085	Income  $80,000 to $84,999	3680	75668
HIXCY8590	Income  $85,000 to $89,999	3057	78725
HIXCY9095	Income  $90,000 to $94,999	2785	81510
HIXCY95100	Income  $95,000 to $99,999	2523	84033
HIXCY10025	Income $100,0000 to $124,999	11234	95267
HIXCY12550	Income $125,000 to $149,999	6186	101453
HIXCY15020	Income $150,000 to $199,999	5015	106468
HIXCY20050	Income $200,000 to $249,999	1969	108437
HIXCY25050	Income $250,000 to $499,999	2463	110900
HIXCYGT500	Income $500,000 Plus	1523	112423

This is giving counts of households in each income range.  How do we calculate a median from the ranges?  Going back to the definition of a median, "a value in an ordered set of values below and above which there is an equal number of values (from dictionary.com)", we first have to find the range where the median point must exist.  There are 112,423 households in the above table. The median HH income is the household where 56,211 households are above and below that one. Looking at the running total above, we can see that the median must be within the $60,000 to $64,999 range.

So now that we know for certainty that the Median Income for Boulder County is between $60K and $65K, we would like to know more specifically what it is.  In reality, we don't have any information about the households in that range.  All 4720 households could be making $60,001 or they could all be making $64,999, so the median really could be anywhere in that range, but we can presume that most areas fit a bell curve and there is a fairly even distribution of households in that range.  We know that the median is 633 households into the range (56211-55578=633) or 13.4 % (100*633/4720=633).  So simplistically we can assume that the median is 13.4% of the way into the range or 670 HHs into the range (.134*5000+60000=60670).

Boulder County Household Income Chart

Now, just because we don't want to be too simple and we want to produce the most accurate result possible, we can take our estimate one step further.  If we look at the above chart of HH incomes in Boulder, we see that there is a major drop off from the $60K-65K group to the $65K-70K group.  Above we presumed that there were an equal # of people making incomes within the entire $60-65K range, but we can assume that there is a falloff within the range the is similar to the falloff between the ranges and that HHs are more concentrated at the low end of the range.  So the RangeMedian function makes a very simplistic model of the curve (it only looks at 1 range above and 1 range below the median range.)  Using a little area under the curve calculus, we can more accurately place the median at 60653.  The exact details of the math are a little too complicated to put here, but the result is always very similar to the above simplistic method, but slightly more accurate.

RangeMedian Formula for calculating Median HH income

RANGEMEDIAN(0,[HIXCYLT05],5000,[HIXCY0510],10000,[HIXCY1015],15000,[HIXCY1520],20000,[HIXCY2025],25000,[HIXCY2530],30000,[HIXCY3035],35000,[HIXCY3540],40000,[HIXCY4045],45000,[HIXCY4550],50000,[HIXCY5055],55000,[HIXCY5560],60000,[HIXCY6065],65000,[HIXCY6570],70000,[HIXCY7075],75000,[HIXCY7580],80000,[HIXCY8085],85000,[HIXCY8590],90000,[HIXCY9095],95000,[HIXCY95100],100000,[HIXCY10025],125000,[HIXCY12550],150000,[HIXCY15020],200000,[HIXCY20050],250000,[HIXCY25050],500000,[HIXCYGT500])