How to find average and median age from an aggregated frequency table 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

 A: 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

