It could be rewritten to handle a range as an argument. I didn't spend the
time on it because I wasn't sure it you needed it.

If you have to average several hundred cells, then I don't think you're going
to get a formula to do it. It would probably be much too long.

It you are interested in an expanded VBA function, let me know. But before I
spend the time on it, I need some pretty specific information about where the
referenced cells are. Are they all in one row or one column? Or a mix of, say,
100 cells in one column, 25 in another, plus a few scattered cells? Will there
ever be any cells that in fact DON'T contain a date/time in the format you
show? Does the routine need to trap out blank cells, cells containing error
values, and cells containing numbers?


On Sat, 31 Jul 2004 08:05:01 -0700, Kurewe
wrote:

Myrna,

Thanks for the input.

Although handling a VBA solution wouldn't be an issue, I neglected to say
that there could be anywhere between 500 to 1000 referenced cells. If I have
to specify each cell individually, this solution would not be a viable one.


"Myrna Larson" wrote:

Can you handle a VBA solution? Go to the VBA editor, select your workbook
over
in the upper left hand pane, go to the Insert menu, and click on Module. In
the blank code pane that appears on the right, paste the following code --
what's between the lines of tildes.

If you average only two times, the VBA code will most likely be slower than
the formula you eventually come up with. But if you need to average more
than
2 times, this function will definitely be easier to use than the
corresponding
formula. The result is in the same format as the inputs.

Two caveats:

(1) It *requires* the commas separating the 3 parts of the text.

(2) The cells to be averaged must be specified individually, i.e.

=AverageTime(A1,A2,A3,A4,A5)

If you write the formula as

=AverageTime(A1:A5)

it won't work. The result will be #VALUE!

Please let me know if this is workable.

'~~~~~~~~~~~~~~~~~~~~~~~~
Option Explicit
Option Base 0

Function AverageTime(ParamArray Durations() As Variant) As String
Dim i As Long
Dim N As Long
Dim Total As Double

N = UBound(Durations)
For i = 0 To N
Total = Total + ConvertToDays(Durations(i))
Next i

AverageTime = ConvertToText(Total / (N + 1))

End Function

Private Function ConvertToDays(vText As Variant) As Double
Dim Divisors As Variant
Dim i As Long
Dim N As Long
Dim Total As Double
Dim W() As String

Divisors = Array(1, 24, 1440)

W = Split(vText, ",")
N = UBound(W())
If N 2 Then N = 2

For i = 0 To N
Total = Total + Val(W(i)) / Divisors(i)
Next i

ConvertToDays = Total

End Function

Private Function ConvertToText(D As Double) As String
ConvertToText = Format$(Fix(D)) & " Days, " _
& Format$(Hour(D)) & " Hrs, " _
& Format$(Minute(D)) & " Min"
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~


On Fri, 30 Jul 2004 18:15:01 -0700, Kurewe

wrote:

JE McGimpsey,

Thank you for the input.

I tried that, using the same examples and the results was "#VALUE!"

Any Ideas?

"JE McGimpsey" wrote:

One way:

Assuming that the days and times are not always 2 digits each:

=TEXT(INT(AVERAGE(LEFT(A1,FIND("Days",A1)-1) + TIME(TRIM(MID(A1,
FIND(",",A1)+1, 3)), TRIM(MID(A1, FIND("Hr,",A1)+3, 3)),0), LEFT(A2,
FIND("Days",A2)-1) + TIME(TRIM(MID(A2, FIND(",",A2)+1,3)), TRIM(MID(A2,
FIND("Hr,",A2)+3,3)),0))), "0_""Days,_""") & TEXT(MOD(AVERAGE(LEFT(A1,
FIND("Days",A1)-1) + TIME(TRIM(MID(A1,FIND(",",A1)+1,3)), TRIM(MID(A1,
FIND("Hr,",A1)+3,3)),0), LEFT(A2, FIND("Days",A2)-1) + TIME(TRIM(MID(A2,
FIND(",",A2)+1,3)),TRIM(MID(A2, FIND("Hr,",A2)+3,3)), 0)),1),
"hh""_Hr,_""mm""_Min""")


I'm sure this is not optimal.




In article ,
Kurewe wrote:

Greetings,

I am working on a spreadsheet that performs several calculations on
the
data
that is provided in another spreadsheet. The basic function of my
spreadsheet
is to make it easy for the recipients to understand the data presented
to
them. Simple enough.

However, one of the sets of data is "turn around time". This data
represents
the time the file is created to the time the file is closed. It is
provided
to me in the following format: "66 Days, 23 Hr, 11Min".

I am trying to take that data and find the average "turn around time"
for
all
the files referenced in this spreadsheet. I have tried, based on the
example
above, to strip the text portions (" Days, " and " Hr, " and "min")
and
leave
it as 66:23:11. As I quickly figured out, this defaults to hh:mm:ss.
No
matter what I have tried, I can not get it to represent dd:hh:mm.

Is there a way to calcualte the following?

A1 = 66 Days, 23 Hr, 11Min
A2 = 66 Days, 23 Hr, 11Min

A3=sum(A1:A2)/2
(which would of course equal "66 Days, 23 Hr, 11Min")