# Averaging times w/fifths of second

#### SteveC

##### Board Regular
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

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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))
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

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

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.

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

Place the code in a regular VBA module.

Replies
3
Views
361
Replies
1
Views
587
Replies
3
Views
1K
Replies
3
Views
987
Replies
0
Views
503

1,219,819
Messages
6,150,404
Members
450,960
Latest member
GB2

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back