Thanks:  0
Likes:  0

# Thread: elapsed times

1. 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.

2. 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?

3. 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%.

4. 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

5. 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.

6. 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

7. Aladin,

I have sent the files to your email.

Looking forward to your assistance.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•