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]

Mala,
Thank you for your reply.
I was attempting to test it but could not because of the error " #NAME? " in the Average column (DQ). I've tried various things but without success. I put your code in a module by itself (in same book).

In your post of the sample spreadsheet, in the average column some averages end in " .5 ". The number to the right of the point is fifths (base10), the only numbers that should appear are from zero to four.

DQ---I copied the =AVg5($DP194:$DP195) through the Average column(DQ) (in this example into DQ195).

DP---I also copied your time formating (x:xx.x) formula through the Finish Times column (DP). If it doesn't matter, is this OK?: =IF($DO195>"*",$DO195,IF(ISNUMBER($DO195),(LEFT($DO195,IF(LEN(INT($DO195))=3,1,2))&":"&MID($DO195,2,2)&"."&LEFT(ROUND(MOD($DO195,INT($DO195))*10,0)))+0,""))
The reason is that your formula causes my conditional formatting in DP to turn all numbers to brown -instead of only numbers with an * to brown.
{Conditional format: _Cell value is__Greater than or equal to__="*"_ The formatting is brown text.)
Your formula in DP is: =IF(ISNUMBER($DO195),TEXT(IF(LEN(INT($DO195))>=3,LEFT(($DO195),LEN(INT($DO195))-2),0),"0:")&TEXT(RIGHT(INT($DO195),2),"00.")&TEXT(($DO195-INT($DO195))*10,"0"),$DO195)

I cut and pasted all the above from your post into my spreadsheet, changing the columns and rows, of course.

FYI: Numbers with asterisk: If there is an asterisk to the right of a number in (the raw times ) DO (e.g. 123.4*), then that asterisk stays with it in DP. Also, it is not necessary to format this number to 1:23.4. A number with an asterisk must *not* be included in any average, and DQ will also be blank: The " =IF($DO195>"*",$DO195 ... " in my DP formula (above in DP---) just copies it over to DP.

FYI: AVERAGE FORMULA IN DQ: This modification to your formula .... =IF(OR($DP195="",$DP195>"*"),"",AVg5($DP194:$DP195)) .... , if it's OK, worked for me previously to assure that any blank cell, or cell number with * , would cause DQ to be blank. The standard Excel Average formula did not include the * numbers in the average. It also, fortunately, did not see a blank cell in DQ as a zero - and therefore did not add zero to the average. My modification also prevented the average from repeating itself in all the blank rows below where the data ended.

I also tried all of your formulas without any modification and in fresh copies of the workbook.
Other than inserting the VBA and the 2 formulae (and change the cell locations in formulae), was I supposed to do anything else? Can you tell I'm a novice? You have great patience. (I'm also glad you can't throw anything at me.) Thank you.

SteveC

 

[Mala]

Hi Steve,
I'll look at this when I return on the 21st. Have to board the train in an hour...so must move. See you on my return.

 

[Aladin Akyurek]

On 2002-10-08 18:31, SteveC wrote:
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

[...]

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

Steve,

That's what I had asked for...

What follows is a formula-based approach that combines what I proposed in this thread and the propsal regarding the averaging part as elaborated by Roger Govier and Myrna Larson in

http://makeashorterlink.com/?O35C51E22

aaAvgTimes SteveC v2.xls
	DN	DO	DP	DQ	DR
187			119.6
188			01:59.3
189			01:59.3
190
191
192			01:59.3
193
194		200.2	02:00.2	120.4
195		155.1	01:55.1	115.2
196		159.1	01:59.1	119.2
197		159.3	01:59.3	119.6
198		200.0	02:00.0	120
199		201.0	02:01.0	121
200		201.4	02:01.4	121.8
201		230.5*	230.5*
202
Sheet1

The original data is in DO194:DO201.

The formula in DP194:DP201

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

a slightly modified version of the one I earlier proposed, converts values like

200.2

to

02:00.2 (with as format: mm.ss.f

where f represents Fifths of a second.

In order to compute an average that takes the Fifths part into account, we convert the values in DP194:DP201 into seconds along with Fifths into Tenths, using Larson's formula, in DQ194:DQ201...

=IF(ISNUMBER(DP194),DP194*86400+MOD(DP194*86400,1),"")

where a value like

02:00.2

becomes

120.4

The formula

=AVERAGE(DQ194:DQ201)

in DP187 computes a value in terms of Tenths.

The formula

=(DP187-(MOD(DP187,1)/2))/86400

in DP188 transforms that result into an average with Fifths.

The formula in DP189 does the same in a single step. It uses the SETV/GETV pair from the morefunc.xll add-in to avoid double computations.

The foregoing method of averaging requires, as is clear from above, an additional, ancillary range.

The array-formula in DP192

=(SETV(AVERAGE(IF(ISNUMBER(DP194:DP201),DP194:DP201*86400+MOD(DP194:DP201*86400,1),"")))-(MOD(GETV(),1)/2))/86400

does not need the ancillary range. This array-formula uses the SETV/GETV pair as before for the same reason.

Note 1. The morefunc.xll add-in is downloadable from:

http://longre.free.fr/english/index.html

Note 2. In order to array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

Aladin
This message was edited by Aladin Akyurek on 2002-10-19 13:00

 

[SteveC]

Aladin,
Thanks for these formulae. I'm going to try them out right away!

Problem: After several searches the only Morefunc.xll that I find is an older one that only lists compatability up to Excel 2000. I have Excel 2002. Do you know if people are using this in 2002 without problems?

Thank you.
SteveC

 

[Aladin Akyurek]

On 2002-10-19 15:08, SteveC wrote:
[...]
Problem: After several searches the only Morefunc.xll that I find is an older one that only lists compatability up to Excel 2000. I have Excel 2002. Do you know if people are using this in 2002 without problems?
[..]

Steve,

I don't expect any problems with 2002 on Windows. It's great and fast add-in to have.

Aladin

 

[SteveC]

Aladin,
<very big grin> I am VERY impressed! I don't see how it can get more efficient than this!
I had confidence in you - and I was rewarded. All comes to he who waits.
By the way, I'm still saving decimals for your birthday.

I have two questions for you:
Is there something simple that can be added to your formula (in DQ) to to have DQ be blank if DP is either blank or if DP has a number with an asterisk (e.g. 1:23.4*)? If not, I can easily use conditional formatting. (I had been using =IF(OR($DP195="",$DP195>"*"),"", ... )

This issue I just discovered today. This "Race Times" list is populated with data from another area of the sheet. This occurs when my "Save Times" button is clicked. The potential problem occurs because a user may also add to this list by manually, inputting to DP - and this would overwrite the formula that takes the raw data ( xxx . x in the hidden DO) and turns it into a time format in DP (x : xx . x). This list is automatically sorted by date (DM thru DP) (when I get the sorting to work) and seems to be a potential problem as data is moved around and re-averaged. What do you think, and is there a solution?
If you've had enough of this, I understand. Your standing as wizard will not be diminished.

Thank you again.
Steve

 

[Aladin Akyurek]

[...]
Is there something simple that can be added to your formula (in DQ) to to have DQ be blank if DP is either blank or if DP has a number with an asterisk (e.g. 1:23.4*)? If not, I can easily use conditional formatting. (I had been using =IF(OR($DP195="",$DP195>"*"),"", ... )

If you use the average formula in DP192, you don't need the DQ range at all!

This issue I just discovered today. This "Race Times" list is populated with data from another area of the sheet. This occurs when my "Save Times" button is clicked. The potential problem occurs because a user may also add to this list by manually, inputting to DP - and this would overwrite the formula that takes the raw data ( xxx . x in the hidden DO) and turns it into a time format in DP (x : xx . x). This list is automatically sorted by date (DM thru DP) (when I get the sorting to work) and seems to be a potential problem as data is moved around and re-averaged. What do you think, and is there a solution?
[...]

I'd suggest to protect the DP column so that the user cannot input anything in that column.

Aladin

 

[SteveC]

Aladin,
Thanks for the reply. Below is part of my spreadsheet with the test data that I use. I figured out that you were refering to your spreadsheet example, but please take a look at mine to be sure I don't have anything unexpected. (The computed results are perfect!)

I will use conditional formatting in the Finish Average column (DQ) so that it will be empty where appropriate.

The user also needs to be able to manually enter data in columns DM thru DO: I left a small, unprotected area at the bottom of the list for the user to manually input (A sort button sorts it up into the list.). If you have a better idea for this please let me know. Otherwise, thank you *very* much for all of your help with this over these several days. I do appreciate it!
Steve


Formulae (from row 195): DM, DN, DO - none.
DP -- =IF($DO195>"*",$DO195,IF(ISNUMBER($DO195),(LEFT($DO195,IF(LEN(INT($DO195))=3,1,2))&":"&MID($DO195,2,2)&"."&LEFT(ROUND(MOD($DO195,INT($DO195))*10,0)))+0,""))
DQ -- {=(SETV(AVERAGE(IF(ISNUMBER($DP$194:$DP195),$DP$194:$DP195*86400+MOD($DP$194:$DP195*86400,1),"")))-(MOD(GETV(),1)/2))/86400}
Data in columns DM thru DO are input and sorted by date as a result of a button elswhere in the sheet.

col . . . . . . DM. . . . . DN . . . . . . DO . . . . . . . DP. . . . . . . . . . . . DQ

row# . . . .DATE . . . .3/4 . . . finish, raw . . . FINISH . . . . FINISH AVERAGE
194 . . . 03/09/84 . . 31.1 . . . . 140.1 . . . . . 1:40.1 . . . . . (min:sec.5ths-of-sec)
195 . . . 03/09/84 . . 44.2 . . . . 155.1 . . . . . 1:55.1 . . . . . . . . . 1:47.4
196 . . . 03/10/84 . . 33.3 . . . . 159.1 . . . . . 1:59.1 . . . . . . . . . 1:51.3
197 . . . 03/11/84 . . 33.4 . . . . 159.3 . . . . . 1:53.3 . . . . . . . . . 1:53.3
198 . . . 03/12/84 . . 35.0 . . . . 200.0 . . . . . 1:54.4 . . . . . . . . . 1:54.4
199 . . . 03/13/84 . . 36.0 . . . . 201.0 . . . . . 2:01.0 . . . . . . . . . 1:55.4
200 . . . 03/14/84 . . 37.0 . . . . 201.4 . . . . . 2:01.4 . . . . . . . . . 1:56.4
201 . . . 03/15/84 . . 38.3*. . . .232.4*. . . . . 232.4*
202 . . . 03/16/84 . . 31.1 . . . . 156.1 . . . . . 1:56.1 . . . . . . . . . 1:56.3

. . .
This message was edited by SteveC on 2002-10-21 03:02

 

[Aladin Akyurek]

On 2002-10-21 02:59, SteveC wrote:
Aladin,
Thanks for the reply. Below is part of my spreadsheet with the test data that I use. I figured out that you were refering to your spreadsheet example, but please take a look at mine to be sure I don't have anything unexpected. (The computed results are perfect!)

I will use conditional formatting in the Finish Average column (DQ) so that it will be empty where appropriate.

The user also needs to be able to manually enter data in columns DM thru DO: I left a small, unprotected area at the bottom of the list for the user to manually input (A sort button sorts it up into the list.). If you have a better idea for this please let me know. Otherwise, thank you *very* much for all of your help with this over these several days. I do appreciate it!
Steve


Formulae (from row 195): DM, DN, DO - none.
DP -- =IF($DO195>"*",$DO195,IF(ISNUMBER($DO195),(LEFT($DO195,IF(LEN(INT($DO195))=3,1,2))&":"&MID($DO195,2,2)&"."&LEFT(ROUND(MOD($DO195,INT($DO195))*10,0)))+0,""))
DQ -- {=(SETV(AVERAGE(IF(ISNUMBER($DP$194:$DP195),$DP$194:$DP195*86400+MOD($DP$194:$DP195*86400,1),"")))-(MOD(GETV(),1)/2))/86400}
Data in columns DM thru DO are input and sorted by date as a result of a button elswhere in the sheet.

col . . . . . . DM. . . . . DN . . . . . . DO . . . . . . . DP. . . . . . . . . . . . DQ

row# . . . .DATE . . . .3/4 . . . finish, raw . . . FINISH . . . . FINISH AVERAGE
194 . . . 03/09/84 . . 31.1 . . . . 140.1 . . . . . 1:40.1 . . . . . (min:sec.5ths-of-sec)
195 . . . 03/09/84 . . 44.2 . . . . 155.1 . . . . . 1:55.1 . . . . . . . . . 1:47.4
196 . . . 03/10/84 . . 33.3 . . . . 159.1 . . . . . 1:59.1 . . . . . . . . . 1:51.3
197 . . . 03/11/84 . . 33.4 . . . . 159.3 . . . . . 1:53.3 . . . . . . . . . 1:53.3
198 . . . 03/12/84 . . 35.0 . . . . 200.0 . . . . . 1:54.4 . . . . . . . . . 1:54.4
199 . . . 03/13/84 . . 36.0 . . . . 201.0 . . . . . 2:01.0 . . . . . . . . . 1:55.4
200 . . . 03/14/84 . . 37.0 . . . . 201.4 . . . . . 2:01.4 . . . . . . . . . 1:56.4
201 . . . 03/15/84 . . 38.3*. . . .232.4*. . . . . 232.4*
202 . . . 03/16/84 . . 31.1 . . . . 156.1 . . . . . 1:56.1 . . . . . . . . . 1:56.3

. . .
This message was edited by SteveC on 2002-10-21 03:02

I see you want a running average, a computation that perfectly justifies working with morefunc add-in.

I also observe that you use the first version conversion formula in DP... That is:

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

Actually, only the ISNUMBER test should be sufficient...

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

You could also use the second version I proposed... That is:

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

Your solution reagrding "manual entry and sorting in" looks good to me.

 

[SteveC]

Aladin,
I switched to your conversion formula and it works fine.
Thank you again.
Steve

May the Decimals be with you