elapsed times

[thare]

8:30:30 8:32:12.09 01:42.09 102
8:33:00 8:34:43.56 01:43.56 104
8:42:00 8:43:12.23 01:12.23 72
8:44:00 0:00:00.00

START FINISH ELAPSED POINTS

From the above example, is there a formula that can find the slowest time( 01:43.56) + 10% or 20% of the above elapsed times and the can be converted to points. (Automatically)

 

[thare]

A stage is a course (with a starting point and a finishing point) illegally racing cars on the road.

10% and 20% is just a rate given to driver that doesn't complete a stage or doesn't bring his car to start the stage, respectively.

 

[Aladin Akyurek]

On 2002-04-10 09:22, thare wrote:
A stage is a course (with a starting point and a finishing point) illegally racing cars on the road.

10% and 20% is just a rate given to driver that doesn't complete a stage or doesn't bring his car to start the stage, respectively.

I'll assume that A2:C5 houses the sample data.

In G1 enter:

=MAX(C2:C5)*86400

In D2 enter and copy down:

=ROUND((LEN(A2)=0)*($G$1+$G$1*20%)+(LEN(B2)=0)*($G$1+$G$1*10%)+(COUNT(A2:B2)=2)*(C2*86400),0)

This formula assumes that there is no start time is entered when a participant fails to start the stage and end time when a participant fails to complete a stage.

This formula is a Boolean OR formula. The same thing can be expressed in an IF formula.

Are the results OK as they stand?

 

[thare]

Dear Aladin.

Thanks for your reply, also thanks to the other that replied. You said that the formula that you gave to me can be expressed in a IF formula.

My meaning:

That if there is NO start but there is a finish time or (the other-way around), the calculation can assume 10% or 20% of the slowest time in the group.

(e.g) below

8:30:30 - 8:32:41.00 =02:11.00 131
8:30:30 - 8:32:41.56 =02:11.56 132
8:32:00 - 0:00:00.00 =00:00.00 0
9:24:00 - 9:26:45.01 =02:45.01 165

start finish elapsed points

Can it calculate and elapsed for the one that doesn't have a time, base on the slowest time (02:45.01) in the group.
Whether it is 10% or 20%.

 

[Aladin Akyurek]

On 2002-04-10 11:37, thare wrote:
Dear Aladin.

Thanks for your reply, also thanks to the other that replied. You said that the formula that you gave to me can be expressed in a IF formula.

My meaning:

That if there is NO start but there is a finish time or (the other-way around), the calculation can assume 10% or 20% of the slowest time in the group.

Yes, the Boolean formula does that. The precondition is, as I stated: Don't enter a finish time (leave the cell empty) when the stage/course is not completed. If a participant fails to start the stage, don't enter a start time (leave the target cell empty). The formula will do the rest. By the way, it computes directly the elapsed time in seconds.

(e.g) below

8:30:30 - 8:32:41.00 =02:11.00 131
8:30:30 - 8:32:41.56 =02:11.56 132
8:32:00 - 0:00:00.00 =00:00.00 0
9:24:00 - 9:26:45.01 =02:45.01 165

start finish elapsed points

Can it calculate and elapsed for the one that doesn't have a time, base on the slowest time (02:45.01) in the group.
Whether it is 10% or 20%.


With respect to the sample data above: it computes for the third participant not a 0, but

=165+165*10%

because 165 is the slowest time to which it adds the penalty of 10%.

If you want, I can send you the workbook that includes the formulas I proposed.

Aladin

 

[thare]

Dear Aladin

I would really appreciate it, if you can do that for me, Sir.

And i'll send you what i'm working with a present.

Thanks again in advance.

 

[Dave Patton]

1. How are points assigned?
2. What is penalty structure.

I assumed that penalties are assigned not just based on time. You can adjust the rank etc to assign relevant points.
Time Seconds Penalty Total
0:01:42 102.0 102.0 5 4
0:01:43 103.0 1 113.3 6 3
0:01:12 72.0 72.0 2 7
0:01:05 65.0 65.0 1 8
0:01:50 110.0 1 121.0 7 2
0.0 2 132.0 8 1
0:01:16 76.0 2 91.2 4 5
0:01:18 78.0 78.0 3 6

seconds =C2*86400
Total =IF(D2=0,MAX(rS)*(1+E2/10),(D2)*(1+E2/10))

Note: a)rS range of Seconds
b)revise to reflect full penalty structure

 

[thare]

Aladin,

I have sent the files to your email.

Looking forward to your assistance.