Averaging times w/fifths of second

[SteveC]

I need help with averaging times that also contain fifths of a second. I have a column of times in DP from row 194 thru 358. Every row in column DQ contains the average for that same row in DP and up to the top (DP194).

This is an example from row 199 of the formula that I have on each row in DQ: =AVERAGE($DP$194:$DP199).
The times are formatted as m:ss.f (minutes : seconds . fifths of a second (base 5)). The problem is that this formula does not take into account that those are fifths of a second to the right of the decimal point, instead of a decimal. Is this possible to do this?

The possible values to the right of the decimal point are: 0, 1, 2, 3, 4 .
Examples of the times in DP that need to be averaged: 1:42.0 , 1:59.4 , 2:02.1
I'm using Office XP.

Thank you for any help.
SteveC

 

[Mala]

There seems to be no direct way to do this - so far as I know, Excel will not go beyond displaying seconds to decimal based on 10.
To display (not use for calculation) the values in a format where the digit after the decimal point represents the fifths of a second, you need to define a custom function as under:

Function Sec5Txt(Tm As Single) As String
FullTime = WorksheetFunction.Floor(Tm, 1 / 86400)
ExtraTime = Tm - FullTime
Parts5F = WorksheetFunction.Floor(ExtraTime, 1 / (86400 * 5))
Parts5C = WorksheetFunction.Ceiling(ExtraTime, 1 / (86400 * 5))
If ExtraTime - Parts5F >= Parts5C - ExtraTime Then Parts5 = Parts5C Else Parts5 = Parts5F
Parts5 = Parts5 / (1 / (86400 * 5))
Sec5Txt = WorksheetFunction.Text(Tm, "hh:mm:ss") & "." & WorksheetFunction.Text(Parts5, "0")
End Function

For row 199 of the formula that you have on each row in DQ, use the formula: =Sec5Txt(AVERAGE($DP$194:$DP199))
instead of just AVERAGE($DP$194:$DP199).
Note that this function returns text - using this with the VALUE() function would interpret the digit after the decimal point as 'tenths' not 'fifths'.

If you wish to use the average values for subsequent calculations (with accuracy as obtained after rounding to fifths) you would need to use the following function

Function Sec5Num(Txt As String) As Date
Time1 = TimeValue(Left(Txt, Len(Txt) - 2))
Time2 = Val(Right(Txt, 1)) / 5 / 86400
Sec5Num = Time1 + Time2
End Function

A typical formula would be =Sec5Num(Ref) where Ref refers to a cell containing string calculated by the Sec5Txt function.


If you wish to use the average values for subsequent calculations with the original accuracy simply use AVERAGE($DP$194:$DP199) again without refering to DQ. I use XL 2K and can't find
the number format hh:mm:ss.f - so I used hh:mm:ss.0

Hope this helps...

_________________
Mala Singh
MrExcel Graphics & Engineering Division
This message was edited by Mala on 2002-10-09 15:34

 

[plettieri]

In the last 7 or 8 days there has been several posts to the very subject on avg time using 5th of seconds....noting several solutions...might try search or looking back about 1 week.....


good luck

pll

 

[SteveC]

Hi Mala and Plettieri,
I did a search -and the one string found is mine.
My original post: http://www.mrexcel.com/board/viewtopic.php?mode=viewtopic&topic=24348&forum=2&start=10

I created this new post in order to separate the problems. However, the old string above continued on this averaging problem.

Mala - The "f" is my own notation for fifths. I failed to explain that.
I don't understand where to place the function, but I'll work on it. Also, I'm copying your post to my original string.

Thanks for your replies.
SteveC

Please post any further replies to my original post ("Averaging times"), above. Thanks.
This message was edited by SteveC on 2002-10-09 19:57

 

[Mala]

Place the code in a regular VBA module.