Getting an average from a column of calculated alphanumeric data

[Alper Takci]

Hello,

I am using a formula that calculates the length of time in Years, Months and Days. The formula is built up in a way that the result comes in as an alphanumeric value such as "2 Years 9 Months 3 Days". This formula is applied to a column of about 5000 lines. What I would like to accomplish is to get the average of the entire column. The beginning date of any row is coming from a cell in that row however the end date may be either today or an end day in that row if there exists one. Below also you can see the formula I am using.

The second step after achieving the average is to put a condition such as if there is a specific word in a cell in a row then the formula should add the calculated length of time from that row to the average calculation.

Hope I was clear enough. Thanks in advance for your help.

The formula:

=IF(M9>0;IF(DATEDIF(L9;M9;"Y")=0;" ";DATEDIF(L9;M9;"Y")&" years ")&IF(DATEDIF(L9;M9;"ym")=0;" ";DATEDIF(L9;M9;"ym")&" months ")&IF(DATEDIF(L9;M9;"md")=0;" ";DATEDIF(L9;M9;"md")&" days");IF(DATEDIF(L9;$H$3;"Y")=0;" ";DATEDIF(L9;$H$3;"Y")&" years ")&IF(DATEDIF(L9;$H$3;"ym")=0;" ";DATEDIF(L9;$H$3;"ym")&" months ")&IF(DATEDIF(L9;$H$3;"md")=0;" ";DATEDIF(L9;$H$3;"md")&" days"))

As you might guess, L9 is the beginning date and M9 is the end date if it exists. H3 is where I store Today().

 

[mole999]

so you have an end date and you have a begin date, so M9-L9 will give you a total which you could use as decimal, then do the average of those values

like this in row 9

=IF(M9="",H3-L9,M9-L9)

 

[Alper Takci]

so you have an end date and you have a begin date, so M9-L9 will give you a total which you could use as decimal, then do the average of those values

like this in row 9

=IF(M9="",H3-L9,M9-L9)

Thanks for the answer. I understand your formula gives me the number of days between the two dates. How would I go about getting the average of the number of years between the two dates in the array M9:M5008 and L9:L5008 in one formula as I cannot use a separate column to average each row and then do another average of the result column?

Also my second question stands; how would I go about adding a condition like gender (eg. "Male") in say C9 to be verified before the length of that specific row is added to the average calculation?

 

[mole999]

=IF(M9="",H3-L9,M9-L9) would place the result of each row in a separate column you nominate, and then you would do an average of that single column. i.e put =IF(M9="",H3-L9,M9-L9) into z9 and drag down, then z8 could become average (z9:z5008) which will give you a decimal value

Pearson Excel Date Functions should help with the different ways of viewing a result of your choosing

 

[Alper Takci]

Not exactly what I am looking for as I do not have the column to spare for a row by row calculation but thank you anyway for your help.

 

[mole999]

I can't think how you will achieve a substitute of blank end dates if you can't spare one of 255 (2003) / 16384 (2007) columns as a helper

 

[Alper Takci]

I can't think how you will achieve a substitute of blank end dates if you can't spare one of 255 (2003) / 16384 (2007) columns as a helper

Well I just wanted to use one smart formula and one cell for this, otherwise, your solution is the safe bet to go with.

Just FYI, if we were talking about regular numbers and not dates with conditions the solution is very simple; {=AVERAGE(L9:L5008-M9:M5008)}. With this, however, I cannot verify whether there is data in any of the rows in column M because if there were none, I would have to go to the TODAY() cell and subtract the beginning date from that. I hope my intention is clearer now and once again, thanks