How to Calculate Average Age in Excel: 7 Methods

Method 1 – Calculate Average Age with Average Command

Steps

Select cell D14. This cell should be below the age column. If the data is in a row instead, it should be adjacent to the right of the data row.
Select Home >> Editing >> AutoSum >> Average, as shown below.

A formula will be inserted automatically.

Press Enter to get the desired result.

If there is a break in the data column, i.e., an empty cell, then the command won’t be able to detect all the data.

Method 2 – Average Age with AVERAGE Function in Excel

Use the AVERAGE function to get the same result. Apply the following formula in cell D14 for that.

=AVERAGE(D5:D12)

Method 3 – Determine Average Age with Excel SUBTOTAL Function

The SUBTOTAL function also allows us to calculate the average age in Excel. Enter the following formula in cell D14 to do that.

=SUBTOTAL(1,D5:D12)

This formula allows you to filter your dataset. 1 indicates the AVERAGE function.

The earlier formula also includes the hidden rows. Enter the following formula instead to exclude the hidden rows.

=SUBTOTAL(101,D5:D12)

Method 4 – Get the Average Age with SUM & COUNT Functions

You can combine the SUM and the COUNT functions for the same results. Enter the following formula in cell D14 for that.

=SUM(D5:D12)/COUNT(D5:D12)

Method 5 – Calculate Average Based on Single Criteria

5.1 Using AVERAGEIF Function

Assume you want to calculate the average age of people over 25. Apply the following formula with the AVERAGEIF function in cell D14.

=AVERAGEIF(D5:D12,">25")

5.2 Combining SUMIF & COUNTIF Functions

Get the same result by combining the SUMIF and the COUNTIF functions. Enter the following formula in cell D14 to do that.

=SUMIF(D5:D12,">25")/COUNTIF(D5:D12,">25")

Method 6 – Calculate Average Age Based on Multiple Criteria

6.1 Using AVERAGEIFS Function

Assume you want to calculate the average age of married people who are more than 25 years old. Enter the following formula with the AVERAGEIFS function to do that.

=AVERAGEIFS(D5:D12,C5:C12,"=Married",D5:D12,">25")

6.2 Combining SUMIFS & COUNTIFS Functions

You can use the following formula with the SUMIFS and the COUNTIFS functions.

=SUMIFS(D5:D12,C5:C12,"=Married",D5:D12,">25")/COUNTIFS(C5:C12,"=Married",D5:D12,">25")

Method 7 – Calculate the Average of Current Ages

You have the birth dates instead of the ages of the people in column D. Enter the following formula in cell D14 (should be formatted as General) to calculate their average age. The TODAY function returns the current date.

=AVERAGE(TODAY()-D5:D12)/365

Things to Remember

You may need to enter the array formulas with CTRL+SHIFT+ENTER.
You can apply the above formulas to calculate the average of any group of numbers.