How to calculate an average duration of time client stayed with us
Question
How to calculate an average duration of time client stayed with us
Hi there, I have a list of clients and want to know the average time they stay with us. I have a column (H) which calculates the years and months each client was with us:
=IF(ISBLANK(G38),DATEDIF(F38,TODAY(),”Y”)&” years and
“&DATEDIF(F38,TODAY(),”YM”)&” months”,DATEDIF(F38,G38,”Y”)&”
years and “&DATEDIF(F38,G38,”YM”)&” months”)
this gives me each clients years and months but it would be good to get an overall average so I can say how long on average a client stays.
Thanks
Rachel Catalogues
London, England
Answer:
I am giving here theree formulas for solution.
I. =IF(ISBLANK(AVERAGE(G1:G34));DATEDIF(AVERAGE(F1:F34);TODAY();”Y”)&” years and “&DATEDIF(AVERAGE(F1:F34);TODAY();”YM”)&” months”;DATEDIF(AVERAGE(F1:F34);AVERAGE(G1:G34);”Y”)&” years and “&DATEDIF(AVERAGE(F1:F34);AVERAGE(G1:G34);”YM”)&” months”)
II. =DATEDIF(AVERAGE(F1:F34);AVERAGE(G1:G34);”y”)&” years”&” “&DATEDIF(AVERAGE(F1:F34);AVERAGE(G1:G34);”ym”)&” months”
III. =DATEDIF(0;INDEX(AVERAGE(G1:G34-F1:F34););”y”)&” years”&” and “&DATEDIF(0;INDEX(AVERAGE(G1:G34-F1:F34););”ym”)&” months”
The file attached with those formulas.
Son Yorumlar