Averaging out data from a DATEDIF Formula

**DarrenH** · 04-10-2008, 03:06 AM

Hi All,

Im going insane trying to figure out how to Average out the data i've accumulated with the DATEDIF Formula...Can anyone please clarify if this is even possible ??

Here's the situation...

I've got a range of data that has been calculated by using the DATEDIF Function (below):

=DATEDIF(H4,I4,"M")&" Months, " &DATEDIF(H4,I4,"md")&" Days"

This is working fine, im getting the desired results (eg: '3 Years, 5 Months')...however, i now need to calculate the average of my DATEDIF Results and none of the formula's i try seem to work ??

I've attached a small example of the Table that needs to be populated...Column E just needs to display the average of all the data in Column D...

Thanks in Advance for any help you can provide.

Cheers - Darren

**DarrenH** · 04-10-2008, 05:31 PM

...Bump...

**ChemistB** · 04-10-2008, 05:36 PM

Once you concatenated your data in column C to achieve your desired formatting, everything becomes a string (text) and thus Excel can't do numerical calculations on it. Can you have a hidden column with the DATEDIF formula without any extraneous formatting and use that to calculate your average?

ChemistB

**daddylonglegs** · 04-10-2008, 05:38 PM

If you want an average of rows 2 to 6 you can use

=DATEDIF(AVERAGE(B2:B6),AVERAGE(C2:C6),"M")&" Months, " &DATEDIF(AVERAGE(B2:B6),AVERAGE(C2:C6),"md")&" Days"

**DarrenH** · 04-10-2008, 05:48 PM

ChemistB - Cheers for the reply.

I may be doing it wrong but i did try:

=DATEDIF(H4,I4,"M")&"."&DATEDIF(H4,I4,"md")

Which gave me a numerical result (eg: 3.2)...however when i try to calculate the average of these results i get the "#DIV/0!" error (i was was just using a straight forward Average function tho, below)

=AVERAGE(O3:O59)

Where am i going wrong, does it have something to do with the "." that i've included into the formula ??

**DarrenH** · 04-10-2008, 05:52 PM

Daddylonglegs - Cheers for the Reply

I'll need to use that piece of Code later on in the spreadsheet im developing, so thanks !!

As for now tho, the data i need to Average out only sits in the one Column.

Im still open to suggestions tho