Averaging out data from a DATEDIF Formula

...Bump...

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

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"

ChemistB - Cheers for the reply.

I may be doing it wrong but i did try:

Please Login or Register  to view this content.

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)

Please Login or Register  to view this content.

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

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

I'm not sure I understand, then. What are you trying to average in E2, what result would you expect for your example?

Hmmm...apologies, I mis-read your reply...

I've used your Code, which will work (i just had to make the cell references Absolute). It's just a completely different way to how i saw it happening.

Just so i can clariy this for future reference:
You cant average out a range of cells if you've used a formula to concatenate the desired result ?

Or, will only work if you combine the Average function into the formula your developing ??

As ChemistB says, you can't average text. If you showed the difference in days only, e.g. by using a formula like this in D2

=C2-B2

then this would be a numeric result which you could average.....or to get an approximation in months

=(C2-B2)/30.5

Incidentally you could get the same result as the previous formula I posted by using

=AVERAGE(B2:B6)

in cell B7 copied across to C7 and then in D7

=DATEDIF(B7,C7,"M")&" Months, " &DATEDIF(B7,C7,"md")&" Days"

Thanks DLL...

I managed to incorporate what you provided to retrieve the results i need...However !!

Some of the Cells in Column C are Blank (eg: the employee hasnt finished their testing yet, so the completion date doesnt exist)...

What can i incorporate into the formula that will "identify when a cell is blank" and then "disregard the entire row" from the Averaging ??

Is this possible to achieve with formula's in Excel, or does it have to be completed through VBA ??

I have limited VBA Knowledge, but with the right code i could set it up to work...

Cheers - Darren

Hi Darren,

To check first if a cell in C is blank, you'll need to add an IF statement to the formula that DLL has given you. IF statements follow the following format

Please Login or Register  to view this content.

So it would look something like this in your case

Please Login or Register  to view this content.

or in English if C2 is blank ("") then leave this cell blank, otherwise perform the formula.

Note: Your formula

Please Login or Register  to view this content.

might look like a number but it's still a string to Excel. If you wanted to average the months and the days like that, you'd need to put each piece into a separate column e.g

Please Login or Register  to view this content.

Hope this helped.

ChemistB

Thanks ChemistB,

Cheers for your help with this one guys - i've got it running like a dream !! (for now)...

Appreciate it

Regards - Darren