I am using excel and I am trying to find both the average age and median age. I have two columns. 1 for the category and the other for the number of people in each category.
Under 5 6,360
5-9 6,360
10-14 10,986
15-17 5,204
18-24 7,886
25-34 9,463
35-44 17,349
45-54 18,926
55-64 13,406
65-74 6,309
75 and over 5,520
total 107,769
General comments: If the union of all bins was a finite interval, you could compute a mean with certain assumptions, or you could get bounds without any assumptions. A common (though often untenable) assumption is uniformity within a bin. If the bins are not wide it can still be a useful approximation.
With an open upper-end bin (75+), you can't compute an average without some strong assumptions. It would be useful to explore sensitivity of the mean estimate to those assumptions.
Usually you can compute a median category, and it's straightforward, so let's begin there.
Median: The median age is the age of the "middle person" (or any value between the middle two people if there's an even number - with binned data you want those two to be in the same bin; fortunately it's rare for a bin boundary to be between them, in which case either bin could be regarded as the median bin; you might choose the boundary itself as the median in that case).
With 107769 people, the age of the (107769 + 1)/2 = 53885-th oldest person is the median age.
Agegroup Count cumsum(age$Count)
1 Under 5 6360 6360
2 5-9 6360 12720
3 10-14 10986 23706
4 15-17 5204 28910
5 18-24 7886 36796
6 25-34 9463 46259
7 35-44 17349 63608
8 45-54 18926 82534
9 55-64 13406 95940
10 65-74 6309 102249
11 75 and over 5520 107769
there are 46259 people aged 34 or younger and 63608 aged 44 or younger, so the median age group is 35-44.
You could go further by making some assumptions to try to make an estimate of the year within that - e.g. if you assume uniform age distribution within bins, the median age would be (53885-46259)/17349 = 43.96% of the way through the range of ages in that age group, which suggests a median age of about 39.4. However, you would need to assess the reasonableness of that assumption. Being close to the mode with what looks like (and probably is) a fairly smooth distribution, it may not be so bad an assumption for a rough approximation]
Some books give formulas by which to calculate an estimate of the median which amount to doing pretty much what I just did, such as a formula like this: median = $L + w\frac{(\frac{n}{2} − c)}{f}$ (where $L$ is the lower limit of the bin containing the median, $w$ is the width of that bin, $n$ is the total population, $c$ is the cumulative count (cumulative frequency) up to $L$ (the end of the previous bin), and $f$ is the count (frequency) in the median bin does pretty much the same thing (aside from the (n+1)/2 vs n/2, it is the same).
Mean: The mean is most often calculated by treating the data as if it occurred at the bin-centers. For the mean, this is equivalent to assuming the data are uniformly spread within each bin.
Clearly this presents a problem with the last category which has no upper bound. Even if you imposed one ("well, let's say nobody lives past 120"), the midpoint is still a terrible estimate of the mean within the group. You can do things like assume the distribution is similar to some population and get estimates from life tables (many countries make life tables available, which allow calculation of the proportion of people alive at each age, say).
You could also simply assume some average (say 80, or 85), and then see how much difference it made. Nine year old (or so) figures from one Western country (one with longer average lifespan than the US) suggests that the average age of males 75+ is 82.2 - If you can't get suitable figures, I'd think assuming 82 and trying 80 and 85 to get some idea of sensitivity to the assumption would be reasonable.
(More complicated assumptions than the ones described here are possible but not as often used)
I realize this is an old post, but since it came up in my quest to find a VBA script to calculate the median from a range of values (values in bins) like the original poster asked for, I thought I'd post my solution to share with others.
I am beginner when it comes to VB so its possible there are easier ways to do some of the things I did. I have included comments to document what each step of my script is doing.
You will need to have at least 2 columns in your spreadsheet/range of values. The first column must be the beginning of the range (i.e. if it is for income values with the first two ranges
$0 to $9,9999
$10,000 to $14,9999
Then the first 2 rows of the first colmun should be 0 and 10000). Example in attached screen shot (highlighted portion would be my input).
The script prompts for the input range of values. You can have multiple columns of population that you want to get the median for. It will also prompt for the cell you want to put the output, median value(s), into.
here is the script
Sub GetMedian()
'have user select the range for the input data
'the first column must be the number for the beginning of the range
'for the bin
'the second column (can have more than one if doing mulitple areas)
'has the population for that bin
Dim UserRange As Range
Prompt = "Select the input range." & vbCrLf & _
vbCrLf & "The first column must be the beginning of the " & _
vbCrLf & "range for the bin. The second column has the " & _
vbCrLf & "population for the bin. You can have more than " & _
vbCrLf & "one column of populations." & _
vbCrLf
Title = "Select a range"
'Display the Input Box
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=Selection.Address, _
Type:=8) 'Range selection
'get current selected range
Dim myString As String
'myString = Selection.Address
myString = UserRange.Address
'go though the columns
'how many columns are in the UserRange?
NoOfCol = Range(myString).Columns.count
'get the output range from the user
Dim OutRange As Range
Prompt = "Select a cell for the output" & vbCrLf & _
vbCrLf & "Values for multiple medians will be " & _
vbCrLf & "returned in a row beginning with the " & _
vbCrLf & "selected cell " & _
vbCrLf
' Display the Input Box
On Error Resume Next
Set OutRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8) 'Range selection
'now get to work
'for loop starts with the first population column (2nd in range)
'end is how many pop columns are in the user input range
For col = 2 To NoOfCol
'set range as an array
Dim myArray() As Variant
myArray = Range(myString).Value
'get sum of pop and divide by 2 to get halfpoint
'initialize a total to aggregate values
popSum = 0
For i = 1 To UBound(myArray)
popSum = popSum + myArray(i, col)
Next
'MsgBox popSum
MedianIndex = popSum / 2
'MsgBox MedianIndex
'initialize for running total
runtotal = 0
'step through each row
For i = 1 To UBound(myArray)
'add the cumulative total pop
runtotal = runtotal + myArray(i, col)
'check if the runtotal exceeds the medianIndex
If runtotal > MedianIndex Then
'get the cumulative pop for the bin just before we exceeded
'so subtract the current array value from the current runTotal
prevTotal = runtotal - myArray(i, col)
'determine how much into the bin it is to reach the medianIndex
howMany = MedianIndex - prevTotal
'get the pop value in the previous bin
Binpop = myArray(i, col)
'determine the pct of how far into this bin the medianIndex falls
pctInto = howMany / Binpop
'determine the span of the bin
'and multiply it by the pct into the bin
MultiSpan = (myArray(i, 1) - myArray(i - 1, 1)) * pctInto
'calculate the median by adding the result to
'the number for the beginning of hte range
Median = myArray(i, 1) + MultiSpan
'put the median in the output cell
OutRange.Offset(0, col - 2).Value = Median
Exit For
End If
Next
Next
End Sub
