8
$\begingroup$

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
$\endgroup$
  • $\begingroup$ I have re-opened this question because I find it possible to interpret it as a request for how to compute (or estimate) means and medians when binned summaries are provided (and not just as a request for simple Excel formulas). It appears to overlap some existing questions substantially, such as stats.stackexchange.com/questions/60256, so consider looking for answers by searching our site. Since you have two open-ended bins ("under 5" and "75 and over") it will help to explain what these data represent. (I am guessing it's an age distribution.) $\endgroup$ – whuber Feb 17 '15 at 22:39
  • $\begingroup$ Sorry i am new to this site. Under 5 is representing all of the children that live in this demographic and the same for 75 and over. Please forgive i did not understand your question correctly. $\endgroup$ – Frank Feb 17 '15 at 22:52
  • $\begingroup$ I was sure I had written an extensive answer covering at least the median half of this question, but I can't locate it. $\endgroup$ – Glen_b Feb 18 '15 at 4:54
  • $\begingroup$ @Glen_b If you do locate it, it would be a good candidate for a duplicate. $\endgroup$ – whuber Feb 18 '15 at 16:15
8
$\begingroup$

General comments: If the union of all bins was a finite interval, you could compute a mean only with certain assumptions. A common (though often untenable) assumption is uniformity within a bin.

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)

| cite | improve this answer | |
$\endgroup$
0
$\begingroup$

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). Screenshot of Excel input file. highlighted portion represents the input range

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
| cite | improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.