Calculating average age of death by month/year in days

[Pictus]

Hi
I have a data set consisting of date of birth and date of death. I have calculated lifespan in days and have got mean and interquartile ranges from that. However, I would like to calculate lifespan based on months and separately years with the result being displayed in days - so I recalculate the mean etc. The premise is that the trend in the quartiles and mean values increase with lower accuracy levels.

I have double checked and the date columns are formatted as date. The equation below is the one for month, I have been trying to modify but haven't got it to work - any advise would be appreciated

=SUM((MONTH(B2:B15)=12)*C2:C15)/SUM(IF(MONTH(B2:B15)=12,1))

where column B is DOB and C is age (lifespan)

 

[FormR]

Hi, welcome to the forum!

I have been trying to modify but haven't got it to work

What does your formula return? an error? the wrong result?

Maybe you could post an example of what you have in the range B2:B15 and what result you would expect for that example?

 

[shg]

How about ...

=SUMPRODUCT((MONTH(B2:B15) = 12) * C2:C15) / SUMPRODUCT(--(MONTH(B2:B15) = 12))

 

[Pictus]

Worked a treat! Thank you!!

I used the above and just changed Month to Year but it came back with a #DIV/0! error I dragged the formula for complete date range (yrs) to ensure I was capturing yrs that definitely had multiple records rather than just one record in a year. What's the tweak?

=SUMPRODUCT((YEAR($D$2:$D$42) = 1991) *$G$2:$G$42) / SUMPRODUCT(--(YEAR($D$2:$D$42) = 1991))

 

[FormR]

I dragged the formula for complete date range

Hi, that formula doesn't change as you drag it to other cells - is it the actual formula you are using?

 

[Pictus]

Yes - the range for DOB and lifespan values doesn't change hence the $ signs. I fixed them when I did the month calculations as it kept altering by one. I tried the formula without the $ and the same error persists. Ideas?

 

[Pictus]

Fixed it - there is no data for the yr 1991 in my dataset!!
Thanks to you both for you help