Averaging times w/fifths of second

SteveC

Board Regular
Joined
Mar 14, 2002
Messages
118
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Mala

MrExcel MVP
Joined
Apr 4, 2002
Messages
627
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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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

Board Regular
Joined
Mar 14, 2002
Messages
118
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
 

Forum statistics

Threads
1,144,510
Messages
5,724,778
Members
422,577
Latest member
madsifonvdijf

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top