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.

data-example

You may also like...

Bir Cevap Yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir

Şu HTML etiketlerini ve özelliklerini kullanabilirsiniz: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>