fastest time calculation

nismo_ssss

New Member
Joined
Sep 10, 2006
Messages
4
hi all
i am currently in a bit of a rut here :lol: , 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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
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}))
 
Upvote 0
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!!! :oops:
Cheers
Matt
 
Upvote 0
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
 
Upvote 0
Peter,
that worked excellent does the job good thanks alot for that :pray:
Cheers
Matt
Glad to help. I wonder if we are related?
- Both from Australia.
- Similar 'surnames' - Yours: _ssss and Mine: _SSs :laugh: :rofl:
 
Upvote 0
:roll: :beerchug:
hey i live in taree
so ur only 2 hrs away i tried for sss ..... but they wouldnt let me ave it :lol:
maybe not directly related but .... we're all in this together arent we? 8-)
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,137
Members
453,642
Latest member
jefals

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top