Forum Discussion
Average of datedif data
Hi, I'm trying (and failing) to average a column of datedif data (expressed as years and months). The formula used to create the datedif entries is =DATEDIF(O46, P46, "y") &" yrs, "&DATEDIF(O46, P46, "ym") &" mths". I need the average for rows 2 through 46, but I can't seem to write anything that works. Many thanks!!
The formula
=DATEDIF(O46, P46, "y") &" yrs, "&DATEDIF(O46, P46, "ym") &" mths"
returns a text value; you cannot calculate the average of text values.
Use the following formula instead in row 2 (in a different column if you prefer):
=DATEDIF(O2, P2, "m")
Fill down to row 46.
Let's say the new formula is in W2:W46.
The average is
=LET(a, AVERAGE(W2:W46), QUOTIENT(a, 12)&" yrs, "&MOD(a, 12)&" mths")
HansVogelaar Thanks so much! That works brilliantly. One further question: is there a way to round up or down the months? Right now the average is coming out at e.g. 3 yrs, 4.276545 mths. Thank you so much!
Although you can't average the differences because they are text and not numbers, you can difference the averages.
= LET( averageStart, AVERAGE(startDate), averageEnd, AVERAGE(endDate), avDifference, DATEDIF(averageStart,averageEnd,"y") & "yr " & DATEDIF(averageStart, averageEnd,"ym") & "mths", avDifference )
