Average Age with months for all ID's

[Anonymous]

Hi ,

i need average of the ages in a text box. the age should be year.months format. for example 1.3 something like that.

Note:i need this is text box not in the chart.

[Anonymous]

round(avg( Age(Today(), Birthdate ) ))

See attached.

[MK_QSL]

=AVG(Aggr(age(Today(),Birthdate) + (if(day(Today()) >= day(Birthdate),

  mod((Year(Today())*12+Month(Today()))-(Year(Birthdate)*12+Month(Birthdate)),12),

  if(mod((Year(Today())*12+Month(Today()))-(Year(Birthdate)*12+Month(Birthdate)),12)=0,11,mod((Year(Today())*12+Month(Today()))-(Year(Birthdate)*12+Month(Birthdate)),12)-1)))/12,MEM_ID))

[Anonymous]

Thank you Bill, can we also include months as well along with number of years

[sunny_talwar]

Not sure if you want this, but check it out.

Best,

Sunny

[Anonymous]

since we are in July, i need the month to be taken as Currentmonth-1.. Can you please help me with that

[MK_QSL]

Replace below instead of Today()

AddMonths(Today()-1)

[Anonymous]

i have to change in all the places

[Anonymous]

i soon as i do that i am getting an error ...

[MK_QSL · Accepted solution]

Today is 17/07/2015... If you want to Calculate from 17/06/2015, use as below

=Avg(Aggr(age(AddMonths(Today(),-1),Birthdate) & ',' & (if(day(AddMonths(Today(),-1)) >= day(Birthdate),

  mod((Year(AddMonths(Today(),-1))*12+Month(AddMonths(Today(),-1)))-(Year(Birthdate)*12+Month(Birthdate)),12),

  if(mod((Year(AddMonths(Today(),-1))*12+Month(AddMonths(Today(),-1)))-(Year(Birthdate)*12+Month(Birthdate)),12)=0,11,

  mod((Year(AddMonths(Today(),-1))*12+Month(AddMonths(Today(),-1)))-(Year(Birthdate)*12+Month(Birthdate)),12)-1))),MEM_ID))

If you want to calculated from End Of Previous Month i.e. 30/06/2015

=Avg(Aggr(age(MonthEnd(Today(),-1),Birthdate) & ',' & (if(day(MonthEnd(Today(),-1)) >= day(Birthdate),

  mod((Year(MonthEnd(Today(),-1))*12+Month(MonthEnd(Today(),-1)))-(Year(Birthdate)*12+Month(Birthdate)),12),

  if(mod((Year(MonthEnd(Today(),-1))*12+Month(MonthEnd(Today(),-1)))-(Year(Birthdate)*12+Month(Birthdate)),12)=0,11,

  mod((Year(MonthEnd(Today(),-1))*12+Month(MonthEnd(Today(),-1)))-(Year(Birthdate)*12+Month(Birthdate)),12)-1))),MEM_ID))