Averaging times

[SteveC]

Hi
Averaging times. I have a column of racing times. The column to its right contains the average of the times starting from that line and every line above it. I was proud of myself for figuring out the formula to do it - until I realized that I was averaging it as if were a decimal number (158.4) instead of time (1:58.4) - One minute : fifty-eight seconds . and four fifths of a second. The last number (e.g. ".4")is from zero through 4 (or blank), and represents fifths of a second (Strange, but it's the way they do it.). The format (there is no choice) that it will have been previously input in and then automatically (by a button) copied to the "Times" column (DO) will be xxx.x . If it helps, I can have the DO and DP columns and their numbers formatted in any manner, if needed.

Is there a way to display an average on each line? Do the times have to be first put into the format of x:xx.x ? (-and if so, then how could I do that by formula or VBA?)
I would appreciate any help!

"Times" column: DO ... "Averages" column: DP ... Lines: 194 through 358
Here's what I *was* using on each line in DP:
=IF(OR($DO198="", $DO198>"*"),"",AVERAGE($DO$194:$DO198))

Exception: Certain times do not get included in any average. There is an asterisk after those (e.g. 202.1*). I was experimenting and found, to my surprise, that $DO194>"*" in my formula worked to find and exclude those times with asterisks. (Works for other characters, also.)

Thanks -SteveC

This was extensively edited on 10/5 09:50
This message was edited by SteveC on 2002-10-08 03:55

 

[SteveC]

Aladin,
Thanks
Steve

TIMES....AVERAGE
02:00.2 02:00.2
01:55.1 01:57.6
01:59.1 01:58.1
01:59.3 01:58.4
02:00.0 01:58.7
02:01.0 01:59.1
02:01.4 01:59.4

formula: =AVERAGE($DP$194:$DP200)

If it's of interest, I wrote out the steps for manual calculation of the averages.
This message was edited by SteveC on 2002-10-08 03:53

 

[SteveC]

Aladin,
After re-reading your request, I believe this is what you wanted:
* The average of the first 7 entries (with decimal as fifths) 1:59.3
* (The average using the fifths amount as if they were 10ths = 1:59.4 (decimal).)
* (The average of ONLY the Fifths in each entry (not using mm or ss) = 2 (from 1.57 fifths rounded up).)

Thanks.
Steve

-------------------------------------
Calculations for averaging ONLY the Fifths in each entry (not mm or ss):
2+1+1+3+4= 11 (5ths) (two of the seven were zero)
x2 = 22 (22 tenths)
22 / 7 entries= 3.14 AVERAGE in 10ths
/2 (for 5ths)= 1.57 AVERAGE in 5ths
Round 1.57 up to TWO fifths = 2
-------------------------------------
-------------------------------------


My method for calculating the average using the all of the data from each entry, below.

STEPS TO CALCULATE AVERAGE
---------------------------------------------------
1) SEPARATLY SUM THE PARTS OF EACH OF THE RACE TIME ENTRIES

(DP194:DP200)
02:00.2
01:55.1
01:59.1
01:59.3
02:00.0
02:01.0
02:01.4

. . . . . . . M . . S . . . F(5ths)
SUM= 11 : 175 . 11
----------------------------------------------------
2) CONVERT MINUTES TO SECONDS and FIFTHS OF A SECOND (base 5) TO DECIMAL (base 10) : SUM THE RESULTS TO ACHIEVE THE COMBINED TOTAL SECONDS FOR ALL ENTRIES

((M x 60) + S) + ((F x 2) /10) = Total Seconds for all Entries
11 x 60 + 175 + 22/10 = (660+175+2.2) = 837.2 Total Seconds ("TS") -in decimal
----------------------------------------------------
3) CALCULATE THE AVERAGE OF THE RACE ENTRIES IN SECONDS

TS/#-of-Entries = AVERAGE IN SECONDS (" AVS ")
837.2/7 = 119.6 AVerage in Seconds -in decimal
----------------------------------------------------
4) CONVERT THE AVERAGE IN SECONDS (AVS DECIMAL) INTO MINUTES : SECONDS . FIFTHS OF A SECOND

.....a) CONVERT SECONDS INTO MINUTES: CALCULATE THE NUMBER OF TIMES THAT AVS IS DIVIDED EVENLY BY 60

AVS/60 = EXTRACT WHOLE MINUTES ONLY (NO fraction or decimal)
119.6/60 = 1 minute (1:xx.x)

.....b) COPY THE REMAINDER OF WHOLE SECONDS (which is less than 60) FROM STEP "a" ABOVE
(119.6 - 60) = 59.6 = 59 seconds (1:59.x)

.....c) CONVERT DECIMAL TO FIFTHS OF A SECOND

[decimal #]/2 = FIFTHS OF A SECOND
6/2 = 3 fifths of a second (1:59.3)

5) RESULT: AVERAGE OF RACE TIME ENTRIES: EXPRESSED IN STANDARD TIME FORMAT - WITH THE FRACTION AS FIFTHS (base 5)

m:ss.f = minutes : seconds . fifths
1:59.3
This message was edited by stevec on 2002-10-09 19:19

 

[SteveC]

I am copying this from an answer to another post on averaging with fifths, that I failed to delete. Sorry.
SteveC

Mala MrExcel MVP Joined: Apr 05, 2002 Posts: 11
Posted: 2002-10-09 15:15

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 ]

 

[Mala]

Thanks Steve - I was just going to update this thread with a reference to my post when I saw you have already done it...

 

[SteveC]

Aladin,
Was the info that I sent "Posted: 2002-10-08 18:31" what you wanted?


Mala,
Thank you for the suggestion, but I couldn't get any part of it to work. I get a six digit # for minutes in the time column (DP) and the #Name? error on one line in the average column (DQ) and the rest of DQ has 6 digit #'s.

Steve
This message was edited by stevec on 2002-10-11 03:02

 

[Mala]

Wait...I'll send you a workbook and a mail later today.

 

[Mala]

Copy the folowing code to a module in the VBA Project of the SAME workbook in which you'll use the functions - not the VBA Project some other workbook.

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

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

Make sure to spell the names of the functions correctly in the formulas - the NAME error indicates either incorrect spelling or inability of Excel to find the function in the VBA project.

If this still doesn't work, send me your workbook (mala_rs_singh@rediffmail.com) and I'll set it up for you

I'll try to upload a sample data set here...

 

[Mala]

Fifths.xls
	A	B	C	D	E	F
1	Time1	Time2	Time3	Average	Str @fifth	Back to dec
2	00:10:03.01	00:10:03.01	00:10:03.01	00:10:03.01	00:10:03.0	00:10:03.0
3	00:10:03.02	00:10:03.02	00:10:03.02	00:10:03.02	00:10:03.0	00:10:03.0
4	00:10:03.03	00:10:03.03	00:10:03.03	00:10:03.03	00:10:03.0	00:10:03.0
5	00:10:03.09	00:10:03.09	00:10:03.09	00:10:03.09	00:10:03.0	00:10:03.0
6	00:10:03.10	00:10:03.10	00:10:03.10	00:10:03.10	00:10:03.1	00:10:03.2
7	00:10:03.11	00:10:03.11	00:10:03.11	00:10:03.11	00:10:03.1	00:10:03.2
8	00:10:03.12	00:10:03.12	00:10:03.12	00:10:03.12	00:10:03.1	00:10:03.2
9	00:10:03.22	00:10:03.22	00:10:03.22	00:10:03.22	00:10:03.1	00:10:03.2
10	00:10:03.23	00:10:03.23	00:10:03.23	00:10:03.23	00:10:03.1	00:10:03.2
11	00:10:03.29	00:10:03.29	00:10:03.29	00:10:03.29	00:10:03.1	00:10:03.2
12	00:10:03.30	00:10:03.30	00:10:03.30	00:10:03.30	00:10:03.2	00:10:03.4
13	00:10:03.31	00:10:03.31	00:10:03.31	00:10:03.31	00:10:03.2	00:10:03.4
14	00:10:03.38	00:10:03.38	00:10:03.38	00:10:03.38	00:10:03.2	00:10:03.4
15	00:10:03.39	00:10:03.39	00:10:03.39	00:10:03.39	00:10:03.2	00:10:03.4
16	00:10:03.40	00:10:03.40	00:10:03.40	00:10:03.40	00:10:03.2	00:10:03.4
17	00:10:03.41	00:10:03.41	00:10:03.41	00:10:03.41	00:10:03.2	00:10:03.4
18	00:10:03.42	00:10:03.42	00:10:03.42	00:10:03.42	00:10:03.2	00:10:03.4
19	00:10:03.43	00:10:03.43	00:10:03.43	00:10:03.43	00:10:03.2	00:10:03.4
20	00:10:03.48	00:10:03.48	00:10:03.48	00:10:03.48	00:10:03.2	00:10:03.4
21	00:10:03.49	00:10:03.49	00:10:03.49	00:10:03.49	00:10:03.2	00:10:03.4
22	00:10:03.50	00:10:03.50	00:10:03.50	00:10:03.50	00:10:04.3	00:10:04.6
23	00:10:03.51	00:10:03.51	00:10:03.51	00:10:03.51	00:10:04.3	00:10:04.6
24	00:10:03.52	00:10:03.52	00:10:03.52	00:10:03.52	00:10:04.3	00:10:04.6
25	00:10:03.53	00:10:03.53	00:10:03.53	00:10:03.53	00:10:04.3	00:10:04.6
26	00:10:03.58	00:10:03.58	00:10:03.58	00:10:03.58	00:10:04.3	00:10:04.6
Fifths

This message was edited by Mala on 2002-10-16 03:08

 

[Mala]

Sample data uploaded...thanks Colo and Ivan!!! A lovely utility this one...

SteveC: Apropos of seeing a 6-digit number in place of time ...don't forget to format the cells 'hh:mm:ss.0'
Send me the workbook if you still have problems..

 

[Mala]

Resending privately sent msg on msgboard:

Hi Steve,
Here's the code for a function to return a string formatted as mm:ss.f for average time. Copy it to a module. See the worksheet displayed below to understand how the data is set up and how the formulas are input.
Please check if it is working correctly and write back.

Function Avg5(Rng As Range) As String
Min = 0
Sec = 0
Fifth = 0
Times = 0
For Each cell In Rng
TV = 0
TV = TimeValue(cell)
If TV > 0 Then
Times = Times + 1
Min = Min + Val(Left(cell, 2))
Sec = Sec + Val(Mid(cell, 4, 2))
Fifth = Fifth + Val(Right(cell, 1))
End If
Next
Secs = Min * 60 + Sec + Fifth / 5
AvgSecs = Secs / Times
Min1 = Int(AvgSecs / 60)
Sec1 = Int(AvgSecs) - Min1 * 60
Fifths = (AvgSecs - Int(AvgSecs)) * 10 / 2
Avg5 = WorksheetFunction.Text(Min1, "00") & ":" & WorksheetFunction.Text(Sec1, "00") & "." & WorksheetFunction.Text(Fifths, "0")
End Function

Replies.xls
	A	B	C	D
1	Raw Value	Formatted	Average
2	226.3	02:26.3	02:26.3
3	153.3	01:53.3	02:10.0
4	229.2	02:29.2	02:16.3
5	133.4	01:33.4	02:05.4
6	227.3	02:27.3	02:10.1
7	231.3	02:31.3	02:13.4
8	242.1	02:42.1	02:17.4
9	246.4	02:46.4	02:21.2
10	252.4	02:52.4	02:24.5
11	130.3	01:30.3	02:19.3
12	143.3	01:43.3	02:16.1
13	209.2	02:09.2	02:15.3
14	118.2	01:18.2	02:11.1
15	209.2	02:09.2	02:11.1
16	138.2	01:38.2	02:08.5
17	204.3	02:04.3	02:08.3
18	145.2	01:45.2	02:07.2
19	209.1	02:09.1	02:07.2
20	158	01:58.0	02:06.5
Fifths (2)