Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-12-2017, 07:57 AM
lyiio lyiio is offline Calculate average age (years) Windows 8 Calculate average age (years) Office 2007
Novice
Calculate average age (years)
 
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 Calculate average age (years) Windows 7 64bit Calculate average age (years) Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
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
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 08-12-2017, 09:11 AM
lyiio lyiio is offline Calculate average age (years) Windows 8 Calculate average age (years) Office 2007
Novice
Calculate average age (years)
 
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 Calculate average age (years) Windows 7 64bit Calculate average age (years) Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

You're welcome
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Tags
average, excel 2007

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate average age (years) how to calculate average time aymanharake Excel 6 02-17-2017 04:22 PM
Calculate average age (years) 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 (years) Calculate average age FTL Excel 1 08-09-2016 06:56 AM
Calculate average age (years) Using Field Codes to Calculate 5 years from a Date vandy Word 1 09-23-2009 11:32 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:45 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft