Calculate the average age from a list of birthdates in Excel VBA

Steps:

1. Set up the Excel sheet with a list of birthdates.
2. Write the VBA code to calculate each person’s age from their birthdate.
3. Calculate the average age.
4. Display the result.

Example VBA Code:

1. Excel Sheet Structure

• Let’s assume birthdates are in column A, from A2 to A10 (you can adjust this range based on your actual data).
• You want the average age to be displayed in cell B1.

2. The VBA Code

Here’s a sample VBA code to perform this task:

Sub CalculateAverageAge()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim totalAge As Double
    Dim numberOfPeople As Long
    Dim age As Integer
    Dim birthdate As Date
    Dim averageAge As Double   
    ' Reference to the active sheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name if necessary   
    ' Find the last row of data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' Initialize variables
    totalAge = 0
    numberOfPeople = 0   
    ' Loop through the birthdates (column A)
    For i = 2 To lastRow ' Start at row 2 to skip headers
        ' Check if the cell contains a valid date
        If IsDate(ws.Cells(i, 1).Value) Then
            ' Get the birthdate
            birthdate = ws.Cells(i, 1).Value
            ' Calculate age
            age = DateDiff("yyyy", birthdate, Date)
            ' Adjust if the birthday hasn't occurred yet this year
            If Month(birthdate) > Month(Date) Or (Month(birthdate) = Month(Date) And Day(birthdate) > Day(Date)) Then
                age = age - 1
            End If
            ' Add the age to the total
            totalAge = totalAge + age
            ' Increment the number of people
            numberOfPeople = numberOfPeople + 1
        End If
    Next i   
    ' Calculate the average age
    If numberOfPeople > 0 Then
        averageAge = totalAge / numberOfPeople
        ' Display the average age in cell B1
        ws.Cells(1, 2).Value = "Average Age: " & averageAge
    Else
        ' If no valid data is found
        ws.Cells(1, 2).Value = "No valid data"
    End If
End Sub

Explanation of the Code:

1. Reference to the active sheet: The code starts by setting a reference to the active worksheet (in this case, « Sheet1 »). If your sheet has a different name, update this line:

Set ws = ThisWorkbook.Sheets("Sheet1")

2. Finding the last row: The lastRow variable is used to find the last row of data in column A (where the birthdates are). This way, the code adjusts automatically if you add or remove data.

3. Calculating the age:

• • The code loops through each cell in column A (from row 2 to the last row of data).
  • For each valid birthdate, it calculates the age using the DateDiff function, which returns the difference in years between the birthdate and the current date.
  • If the birthday hasn’t occurred yet this year, the code subtracts 1 from the age to adjust it.

4. Calculating the average age:

• • The sum of ages is stored in totalAge, and the number of people is counted in numberOfPeople.
  • After the loop, if any valid birthdates were found, the average age is calculated by dividing the total age by the number of people.

5. Displaying the result:

• • If there are valid dates, the average age is displayed in cell B1.
  • If no valid data is found, the message « No valid data » is shown in cell B1.

How to Run This Code:

1. Open the VBA editor:
   • In Excel, press Alt + F11 to open the VBA editor.
2. Insert a Module:
   • Go to Insert > Module to add a new module.
3. Copy and Paste the Code:
   • Paste the provided VBA code into the new module.
4. Run the Code:
   • Press F5 or go to Run > Run Sub/UserForm to execute the macro and calculate the average age.

After running the code, the average age will be displayed in cell B1 of your worksheet. If there’s no valid data in column A, the cell will display « No valid data ».