Minutes:Seconds.Hundredths Swim Times Averaged

[AZSwimCoach]

Hi, I'm a swim coach out in AZ who uses filemaker to keep track of my team. I've been using it for records and personal info, but now I'd like it to do some work at a swim meet. For each swimmer there are two stopwatches. The average time is what gets recorded for the meet. We have been calculating times by hand with a calculator and then entering them into filemaker. I know that seems crazy but I just can't figure out how to average times. The time format for swim is Min:Sec.Hundredths

With help from the web I did this and it works most of the time. It rounds nicely and formats things with the correct M:S.H format, but it drops zeros that are leading and not significant when the time is broken up to be worked upon. So the error only shows up with certain number combinations.

I have the following fields in FM6

LN1_Time This is the calculated average time

LN1_Time_01 This is the time from stopwatch one

LN1_Time_02 This is the time from stopwatch two

Int(Round((LeftWords(LN1_Time_01, 1) * 6000 + Case(WordCount(LN1_Time_01) > 1, RightWords(LN1_Time_01, 1) * 100) + LeftWords(LN1_Time_02, 1) * 6000 + Case(WordCount(LN1_Time_02) > 1, RightWords(LN1_Time_02, 1) * 100))/ 2, 0) / 6000) & ":" & Int(Round(Mod(Round((LeftWords(LN1_Time_01, 1) * 6000 + Case(WordCount(LN1_Time_01) > 1, RightWords(LN1_Time_01, 1) * 100) + LeftWords(LN1_Time_02, 1) * 6000 + Case(WordCount(LN1_Time_02) > 1, RightWords(LN1_Time_02, 1) * 100))/ 2, 0), 6000) / 100, 2)) & "." & Right(Round((LeftWords(LN1_Time_01, 1) * 6000 + Case(WordCount(LN1_Time_01) > 1, RightWords(LN1_Time_01, 1) * 100) + LeftWords(LN1_Time_02, 1) * 6000 + Case(WordCount(LN1_Time_02) > 1, RightWords(LN1_Time_02, 1) * 100))/ 2, 0), 2)

1:29.75 and 1:30.09 equal 1:29.92 so.... it yields a correct answer

1:00.73 and 1:00.56 equal 1:0.65 while here it drops a place holding zero

1:07.07 and 1:07.16 equal 1:7.12 again it drops the place holding zero

I've worked on this forever--well bucket loads of hours--and all I ever get is close to right. Any help would be appreciated.

[comment]

Are the fields LN1_Time_01 and LN1_Time_02 of type Time? If so, you can make the calculation field =

Average ( LN1_Time_01 ; LN1_Time_02 )

Set the result type to Time and format the field as desired.

[AZSwimCoach]

Are the fields LN1_Time_01 and LN1_Time_02 of type Time? If so, you can make the calculation field =

Average ( LN1_Time_01 ; LN1_Time_02 )

Set the result type to Time and format the field as desired.

Hi, thanks for your effort. I do not know if they added this in later versions of Filemaker, but I cannot do that in Filemaker6. I need to keep the format of Min:Sec.Hundredths and the time field will not allow that. (mm:ss.hh the colon and period are the separators)

[AZSwimCoach]

I just had a brainstorm. Rather than fight with the math, I think I could use the formula I have but then use the logical/text functions to simply insert a zero where it is needed. Since the only problem makers are...

mm:0.hh --> mm:00.hh

mm:1.hh --> mm:01.hh

mm:2.hh --> mm:02.hh

mm:3.hh --> mm:03.hh

mm:4.hh --> mm:04.hh

mm:5.hh --> mm:05.hh

mm:6.hh --> mm:06.hh

mm:7.hh --> mm:07.hh

mm:8.hh --> mm:08.hh

mm:9.hh --> mm:09.hh

Any thoughts? I'll be working on this for a bit.

[comment]

Sorry, I haven't noticed your version. This is really awkward to do in version 6, so let me split it into two calculation fields:

cAvgNum (result is Number) =

Average( 

60 * LeftWords ( TimeA, 1 ) + RightWords ( TimeA, 1 ) / 10 ,

60 * LeftWords ( TimeB, 1 ) + RightWords ( TimeB, 1 ) / 10 

)

cAvg (result is Text) =

Int ( cAvgNum / 60 ) 

& ":" & 

Right( "00" & Int ( Mod ( cAvgNum, 60 ) ), 2 ) 

& 

Mod( cAvgNum, 1 )

[AZSwimCoach]

Sorry, I haven't noticed your version. This is really awkward to do in version 6, so let me split it into two calculation fields:

cAvgNum (result is Number) =

Average( 

60 * LeftWords ( TimeA, 1 ) + RightWords ( TimeA, 1 ) / 10 ,

60 * LeftWords ( TimeB, 1 ) + RightWords ( TimeB, 1 ) / 10 

)

cAvg (result is Text) =

Int ( cAvgNum / 60 ) 

& ":" & 

Right( "00" & Int ( Mod ( cAvgNum, 60 ) ), 2 ) 

& 

Mod( cAvgNum, 1 )

Wow, you are not kidding it's awkward. I wrote it over 2 years ago and have fiddled with it ever since. I think my example at the top is the same idea as yours except I converted to 6000 time slices since I needed the hundredths.

I'll try yours and see how it goes.

Also, I'll see if I can break mine into two pieces to get the result as a number.... and see if that keeps the zero. Then do the second part.

I have had some luck in the last hour tinkering with replace :0. with :00. I just have not had enough practice lately to write the formulas as smoothly as when I was in the middle of the project.

Thanks for your help,

[AZSwimCoach]

Solved but ugly. I'll try a more elegant solution later.

Since

Int(Round((LeftWords(LN1_Time_01, 1) * 6000 + Case(WordCount(LN1_Time_01) > 1, RightWords(LN1_Time_01, 1) * 100) + LeftWords(LN1_Time_02, 1) * 6000 + Case(WordCount(LN1_Time_02) > 1, RightWords(LN1_Time_02, 1) * 100))/ 2, 0) / 6000) & ":" & Int(Round(Mod(Round((LeftWords(LN1_Time_01, 1) * 6000 + Case(WordCount(LN1_Time_01) > 1, RightWords(LN1_Time_01, 1) * 100) + LeftWords(LN1_Time_02, 1) * 6000 + Case(WordCount(LN1_Time_02) > 1, RightWords(LN1_Time_02, 1) * 100))/ 2, 0), 6000) / 100, 2)) & "." & Right(Round((LeftWords(LN1_Time_01, 1) * 6000 + Case(WordCount(LN1_Time_01) > 1, RightWords(LN1_Time_01, 1) * 100) + LeftWords(LN1_Time_02, 1) * 6000 + Case(WordCount(LN1_Time_02) > 1, RightWords(LN1_Time_02, 1) * 100))/ 2, 0), 2)

 works in all but ten cases, I'll keep it for now.



Then, to fix the ten cases where a leading zero is dropped, I'll add

Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(LN1_Time, ":0.", ":00."), ":1.", ":01."), ":2.", ":02."), ":3.", ":03."), ":4.", ":04."), ":5.", ":05."), ":6.", ":06."), ":7.", ":07."), ":8.", ":08."), ":9.", ":09.")

. This fixes the problem since mm:s.hh (only one digit between the separators) is never correct in a mm:ss.hh format. As of now, I added a new field to hold the corrected version where a leading zero is inserted only if it is missing.

I'll let you know how the other option you suggested works out when I have time to play a bit more.

Thanks, I just needed to get into problem solving mode and think deeply. Thanks for helping me with this.