Count average age of when contract was signed, per year....

[weissan]

Hi guys,

I have a potentially stupid question that I can't get my head around...

I have a list of contracts from a time period of 40 years (start and end date in two columns) and one column with the corresponding date of birth of the person connected to the contract. What I want to do is to calculate, for each and every year, the average age of the persons the year they signed the contract.

In other words, I'd like to have one cell for each relevant year and find a formula for that cell that looks at the column with contract start dates and if the date is relevant, counts/sums the age of the persons (date of birth minus the date the contract was signed). I can then do the average in another step, as I also have a column with the number of contracts signed per year.

I hope this is not too confusing! I'd really appreciate some help!

 

[GlennUK]

If you have an extra column for age, for example using DATEDIF:
=DATEDIF(C2,A2,"Y")
assuming column C is Date Of Birth and column A is Start Date Of Contract, then you can do a PivotTable to get what you want ... group the Start Date Of Contract by Year, and choose average of Age as the data field.

 

[weissan]

If you have an extra column for age, for example using DATEDIF:
=DATEDIF(C2,A2,"Y")
assuming column C is Date Of Birth and column A is Start Date Of Contract, then you can do a PivotTable to get what you want ... group the Start Date Of Contract by Year, and choose average of Age as the data field.

Thanks for the input. I don't want to use pivot tables for several reasons. But now I have a column with TRUE/FALSE for every contract - TRUE for first timers, FALSE for non-first timers. So now I'm trying to use SUMIF or SUMPRODUCT to get Excel to calculate avg age when signing the contract, IF there is TRUE in the TRUE/FALSE column....

 

[GlennUK]

Have you got an extra column for the age now too, using the formula that I suggested?

 

[weissan]

Have you got an extra column for the age now too, using the formula that I suggested?

Yepp, yepp - it's working nicely! Thanks a lot!

 

[GlennUK]

Then how about something like this:

=SUMPRODUCT((YEAR(Start_Dates)=1971)*(Age)*(First_timers))/SUMPRODUCT((YEAR(Start_Dates)=1971)*(First_timers))

... for year 1971 for example. Using range names for the Start Dates, the Age calcs, and the TRUE/FALSE First timer flags.