How to Calculate Average Age in Excel (Including Criteria)

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates how to calculate the average age in excel. The average age of a group of people can be easily calculated by adding their age and then dividing the sum by the number of people in that group. But this can be very tedious to do manually for a large set of data. In that case, you should do that in excel instead. Here, you will learn 7 different ways to calculate the average age of a group of people in excel.


Download Practice Workbook

You can download the practice workbook from the download button below.


7 Ways to Calculate Average Age in Excel

Assume you have the following dataset. Then follow the methods below to calculate the average age of this group of people.


1. Calculate Average Age with Average Command

Follow the steps below to calculate the average age using the Average command in excel.

📌 Steps

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

  • After that, a formula will be inserted automatically.

  • Now press Enter to get the desired result as follows.

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


2. Average Age with AVERAGE Function in Excel

Alternatively, you can use the AVERAGE function to get the same result. Apply the following formula in cell D14 for that.

=AVERAGE(D5:D12)

Calculate Average Age with Excel AVERAGE Function


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)

Calculate Average Age with Excel SUBTOTAL Function

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

  • The earlier formula includes the hidden rows also. Enter the following formula instead to exclude the hidden rows.
=SUBTOTAL(101,D5:D12)


4. Get the Average Age with SUM & COUNT Functions

Alternatively, you can combine the SUM and the COUNT functions to get the same results. Enter the following formula in cell D14 for that.

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

Calculate Average Age with SUM & COUNT Functions in Excel


5. Calculate Average Based on Single Criteria

5.1 Using AVERAGEIF Function

Now assume you want to calculate the average age of the people who are more than 25 years old. Then, apply the following formula with the AVERAGEIF function in cell D14.

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

Average Age with Excel AVERAGEIF Function


5.2 Combining SUMIF & COUNTIF Functions

Alternatively, you can 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")


6. Calculate Average Age Based on Multiple Criteria

6.1 Using AVERAGEIFS Function

Now assume you want to calculate the average age of the people who are married and more than 25 years old. Then 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

Alternatively, 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")


7. Calculate Average of Current Ages

Now imagine you have the birth dates instead of the ages of the people in column D. Then enter the following formula in cell D14 (should be formatted as General) to calculate their average age. Here, the TODAY function returns the current date.

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

Calculate Average Age from Birth Dates in Excel


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 too.

Conclusion

Now you know how to calculate the average age in excel in 7 different ways. Which method do you prefer the most? Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about excel related problems and solutions. Stay with us and keep learning.

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo