# fastest time calculation

#### nismo_ssss

##### New Member
hi all
i am currently in a bit of a rut here , first i will explain what i am doing:
i am doing a scoring prgram for a rallysprint which i need to get two of the fastest times out of three different times.
For Example:
in mins & secs
2.32, 3.10, 2.20 therefore fastest time = 4.52

i want to be able to get the two lower times to get a total for the fastest times out of 3
Any help would be greatly appreciated
i've looked around but cant seem to find anything remotely close to this, maybe i am just not looking in the right spot!
Cheers
Matt

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
nismo_ssss

Welcome to the Mr Excel board!

See if this is any use.
1. I formatted A2:D4 as a custom time as follows. mm:ss
2. Times entered as 0:2:32 etc
3. Formula in D2 (copied down) is =SUM(A2:C2)-MAX(A2:C2)
Mr Excel.xls
ABCDE
1Time 1Time 2Time 3Fastest Two
202:3203:1002:2004:52
305:5501:5502:5004:45
403:3003:3003:3007:00
5
Fastest Times

Here is another option. The benefit of this approach is that if you need the lowest 2 out of more than 3, the only change would be to the range:

=SUM(SMALL(A2:C2,{1;2}))

formula worked ... more problems to solve

Seti & Peter_SSs
Thanx very muchly for your help it worked perfectly now i have that sorted out i realised that i came across another problem & that is i need to put their rank of the fastest time outright in the very last column i do so that they can see their positions, i have spent a good hour mucking around with formulas that i thought would work but i just cant get it right so i was wondering if you might be able to help me with that?
for example:

car 1: 2.22, 3.05, 2.20 total 7.47 position 1
car 2: 2.32, 3.55, 2.50 total 9.17 position 3
car 3: 2.35, 3.45, 2.30 total 8.50 position 2

i dont want to change the order of the cars i want to keep them in the same spot i want to put their rank after their total time so they can see what position they are running at
Sorry to be a pain guys, i'm just not as good at this program as i first thought!!!
Cheers
Matt

Matt

Is this what you want for the Position?
Formula in E2 (copied down): =RANK(D2,\$D\$2:\$D\$4,1)
Mr Excel 06 09 10.xls
ABCDEF
1Time 1Time 2Time 3Fastest TwoPosition
202:3203:1002:2004:522
305:5501:5502:5004:451
403:3003:3003:3007:003
5
Fastest Times

Peter,
that worked excellent does the job good thanks alot for that ray:
Cheers
Matt

Peter,
that worked excellent does the job good thanks alot for that ray:
Cheers
Matt
Glad to help. I wonder if we are related?
- Both from Australia.
- Similar 'surnames' - Yours: _ssss and Mine: _SSs

hey i live in taree
so ur only 2 hrs away i tried for sss ..... but they wouldnt let me ave it
maybe not directly related but .... we're all in this together arent we?

I hope you'll forgive me for resurrecting a nearly 7-year-old thread, but I have a problem nearly identical to the one posted by nismo_ssss, with one additional twist:

Like nismo_ssss, I need to identify, sum, and rank the two fastest times out of a series - except I need the fastest two out of four (instead of three). And instead of lap times, they're times of individual runs in a ski/snowboard/skateboard race.

Normally Seti's solution would work perfectly - except for this difference: Occasionally a racer fails to finish a run (for example, by missing a required turn or by crashing), in which case he/she gets "no time" or a blank for that run. And it seems the SMALL function doesn't work with non-numerical values; with Seti's formula, Excel returns a #NUM! error if it hits one of them.

Any suggestions on how to keep that from happening?

Welcome to the MrExcel board!

I'm only finding a problem if there isn't at least 2 numerical values, and you haven't said if that is possible for you, or what result you want if that situation occurs.

Excel Workbook
ABCDEF
1Time 1Time 2Time 3Time 4Fastest Two
2no time1:552:50no time4:45
33:303:30no time3:296:59
42:323:102:203:014:52
5no time1:32no timeno time#NUM!
6no timeno timeno timeno time#NUM!
Fastest Times

Replies
8
Views
649
Replies
3
Views
239
Replies
8
Views
431
Replies
14
Views
1K
Replies
8
Views
352

1,207,423
Messages
6,078,441
Members
446,338
Latest member
AliB

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