# Averaging times

#### SteveC

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

### 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.
[...]
until I realized that I was averaging it as if were a decimal number (184.3) instead of time (1:84.3)[...]

I don't understand how 1:84.3 is supposed to read...

Care to state in words what the original 184.3 means?

1:84.3 ... Oh no, I see what you mean! I am sorry to waist your time with an impossible example. (My only excuse is that it was 2 in the morning.)

Let me explain it with a realistic number like 1:58.4 - - One minute:58 seconds. The ".4" represents fifths, which in this case is four fifths of a second (I know, it's wierd.). The format that it will have been input in and then automatically (by a button) copied to the "Times" column (DO) will be 158.4 . If it helps, I can have the DO and DP columns and their numbers formatted in any manner, if needed.

I have edited my original post (10/5 02:02) for corrections and clarity. I apologize for my mistakes.

Thank you for help.
SteveC
This message was edited by SteveC on 2002-10-05 12:55

On 2002-10-05 12:50, SteveC wrote:
1:84.3 ... Oh no, I see what you mean! I am sorry to waist your time with an impossible example. (My only excuse is that it was 2 in the morning.)

Let me explain it with a realistic number like 1:58.4 - - One minute:58 seconds. The ".4" represents fifths, which in this case is four fifths of a second (I know, it's wierd.). The format that it will have been input in and then automatically (by a button) copied to the "Times" column (DO) will be 158.4 . If it helps, I can have the DO and DP columns and their numbers formatted in any manner, if needed.

I have edited my original post (10/5 02:02) for corrections and clarity. I apologize for my mistakes.

Thank you for help.
SteveC
This message was edited by SteveC on 2002-10-05 12:55

You could convert values in \$DO\$194:\$DO198 in the next column (insert a new column which becomes DP) applying...

=IF(ISNUMBER(DO194),(LEFT(DO194,IF(SEARCH(".",DO194)=4,1,2))&":"&MID(DO194,2,2)&"."&60/RIGHT(DO194,LEN(DO194)-SEARCH(".",DO194)))+0,"")

and average the new column range just with

=AVERAGE(DP194:DP196)

Custom format the inserted column cells and the cell for the average formula as...

[mm]:ss.00

See...
Book4
DNDODPDQ
191
19202:16.12
193
194158.401:58.15
195245.8*
196234.602:34.10
197
198
Sheet1

Hi
Aladin, I followed your instructions but had some problems. This is a sample of what I input and the results:

columns-> .DO.............DP.....................DQ
row#. . . TIMES . converted format . AVERAGE
194. . . . 200.2 . . . 02:00.30 . . . . . . 02:00.30
195. . . . 155.1 . . . 01:55.60 . . . . . . 01:57.95
196. . . . 159.1 . . . 01:59.60 . . . . . . 01:58.50
197. . . . 159.3 . . . 01:59.20 . . . . . . 01:58.68
198. . . . 200.0 . .. #VALUE!. . . . . . . #VALUE!
199. . . . 201.0 . .. #VALUE!. . . . . . . #VALUE!
200. . . . 201.4 . .. 02:01.15 . . . . . . . #VALUE!

Converting the raw data ("Times" column) to time format produces incorrect data: "200.2" (with ".2") should convert to "02:00.2" instead of "02:00.30" . This is because the ".2" actually represents fifths, so it should still be ".2" (two fifths). Same with the other conversions. Note that if the raw data ended in zero, the error "#VALUE!" occurred in the converted times column (DP). This then created the "#VALUE!" error in the "Average" column - all the way to the bottom.
If it's not possible to average fifths this way, can one of the formulae convert fifths to decimal (.1 = .20; .2=.40; .3=.60; .4=.80) , do the averaging, then convert it back to fifths in the "Average" column?
I'm not familiar with parts of the formula.

Column DO contains the raw data. . . Using example of row 196 for the following: Column DP contains the formula (cut & pasted here): =IF(ISNUMBER(DO196),(LEFT(DO196,IF(SEARCH(".",DO196)=4,1,2))&":"&MID(DO196,2,2)&"."&60/RIGHT(DO196,LEN(DO196)-SEARCH(".",DO196)))+0,""). . . Column DQ contains the formula which tested OK (cut & pasted here): =AVERAGE(\$DP\$194:\$DP196)

If you (or anyone else) has a solution for the conversion of DO (raw data) to DP (time format), I would appreciate it. In the raw data, the .1 .2 .3 and .4 are the number of fifths of a second. (e.g. "159.3" = One minute, fifty-nine and three-fifths seconds.)

SteveC
This message was edited by SteveC on 2002-10-06 03:46

On 2002-10-06 03:39, SteveC wrote:
Hi
Aladin, I followed your instructions but had some problems. This is a sample of what I input and the results:[...]

I see... I didn't pay attention to numbers without a dot, that is, the ones like 200.0... I'll modify the formula, but:

Are you distributing your workbook? I'm asking this to assess whether it's possible for you to install the morefunc.xll (non-Microsoft) add-in in your computing environment.

I'm surprised you're up! It's 1:20am here.
This is directed mainly for one person at the moment - who is 7 hours away. However, he needs to be able to use it in case he doesn't have access to his computer. I have mixed feelings about it since I want this ultimately to be useable to anyone. Also, I don't know anything about it.
Thank you for your help here.
SteveC
This message was edited by SteveC on 2002-10-06 04:31

On 2002-10-06 04:28, SteveC wrote:
I'm surprised you're up! It's 1:20am here.
This is directed mainly for one person at the moment - who is 7 hours away. However, he needs to be able to use it in case he doesn't have access to his computer. I have mixed feelings about it since I want this ultimately to be useable to anyone. Also, I don't know anything about it.
Thank you for your help here.
SteveC
This message was edited by SteveC on 2002-10-06 04:31

OK, we'll do it without the add-in...

The conversion formula as modified is...

=IF(ISNUMBER(DO194),(LEFT(DO194,IF(LEN(INT(DO194))=3,1,2))&":"&MID(DO194,2,2)&"."&LEFT(ROUND(MOD(DO194,INT(DO194))*10,0)))+0,"")

See the figure...
aaAvgTimes SteveC.xls
DODPDQDR
191171.4667
19201:59.441
1931
194200.202:00.2011
195155.101:55.10
196159.101:59.10
197159.301:59.30159
198200.002:00.00200
199201.002:01.00
200201.402:01.40
201230.5*
202
Sheet1

Thank you for your help. It worked fine.
My problem now is calculating the averages with fifths of a second. I will make a new post for that now.
Thanks again.
SteveC

On 2002-10-08 03:11, SteveC wrote:
Thank you for your help. It worked fine.
My problem now is calculating the averages with fifths of a second. I will make a new post for that now.
Thanks again.
SteveC

I see I overlooked again something...

What would be the average of just the decimals parts in DP194:DP200 from the last figure?

Replies
0
Views
297
Replies
10
Views
225
Replies
1
Views
334
Replies
6
Views
655
Replies
2
Views
1K

1,217,448
Messages
6,136,692
Members
450,024
Latest member
Beagle263

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