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.


How to Calculate Average Age in Excel: 7 Effective Ways

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

Read More: How to Calculate Retirement Age in Excel


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

Read More: Metabolic Age Calculator in Excel


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.

Download Practice Workbook

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


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. Stay with us and keep learning.


Related Articles


<< Go Back to Calculate Age | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo