Md. Shamim Reza
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)
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. 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)
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")
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
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.
