Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 08-12-2017, 07:57 AM
lyiio lyiio is offline Windows 8 Office 2007
Novice
 
Join Date: Aug 2017
Posts: 2
lyiio is on a distinguished road
Question Calculate average age (years)

Hi,

I have a task in excel 2007 more or less like this:



I am asked to calculate the average age (years) of total, male and female. I was just wondering, is there any way to simply fill in the blanks without generating any extra colomns?

I tried a few times, for total average it could be:

Quote:
=DATEDIF(AVERAGE(C2: C10),TODAY(),"y")
(please let me know if I did it wrong). But for male and female I failed to do it in a nested way.




In case any of you drop a hint, a thousand thanks in advance!
Reply With Quote
  #2  
Old 08-12-2017, 09:05 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,841
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

perhaps something like =sumproduct((datedif(c2:c10,today(),"y"),--(b2:b10="m"))/countif(b2:b10,"m")

for men
replace "m" with "f" for women
Reply With Quote
  #3  
Old 08-12-2017, 09:11 AM
lyiio lyiio is offline Windows 8 Office 2007
Novice
 
Join Date: Aug 2017
Posts: 2
lyiio is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
perhaps something like =sumproduct((datedif(c2:c10,today(),"y"),--(b2:b10="m"))/countif(b2:b10,"m")

for men
replace "m" with "f" for women

Thank you so much for you reply!

I switched the two parts in sumproduct now it works! thank you and hugs!!!!!!!!!
Reply With Quote
  #4  
Old 08-13-2017, 12:12 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,841
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

You're welcome
Reply With Quote
Reply

Tags
average, excel 2007
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to calculate average time aymanharake Excel 6 02-17-2017 04:22 PM
How to calculate weighted average in table? alexsimpson Word Tables 3 12-01-2016 03:19 PM
dynamic ranges to calculate average teatree Excel 1 11-11-2016 10:11 AM
Calculate average age FTL Excel 1 08-09-2016 06:56 AM
Using Field Codes to Calculate 5 years from a Date vandy Word 1 09-23-2009 11:32 AM


All times are GMT -7. The time now is 06:57 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft