Microsoft Office Forums Calculate average age (years)
 Register FAQ Search Today's Posts Mark Forums Read

#1
08-12-2017, 07:57 AM
 lyiio Windows 8 Office 2007 Novice Join Date: Aug 2017 Posts: 2
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!
#2
08-12-2017, 09:05 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Moderator Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,090

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

for men
replace "m" with "f" for women
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
#3
08-12-2017, 09:11 AM
 lyiio Windows 8 Office 2007 Novice Join Date: Aug 2017 Posts: 2

Quote:
 Originally Posted by Pecoflyer 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!!!!!!!!!
#4
08-13-2017, 12:12 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Moderator Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,090

You're welcome
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)

 Tags average, excel 2007

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post aymanharake Excel 6 02-17-2017 04:22 PM alexsimpson Word Tables 3 12-01-2016 03:19 PM teatree Excel 1 11-11-2016 10:11 AM FTL Excel 1 08-09-2016 06:56 AM vandy Word 1 09-23-2009 11:32 AM

All times are GMT -7. The time now is 07:49 AM.

 -- Default Style -- Mobile Style Contact Us - Privacy Statement - Top