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