fastest time calculation

[nismo_ssss]

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

 

[JBH123]

Thanks so much for the quick reply.

It's rare that someone would get a "DQ" (short for disqualification) more than twice out of four runs, but it has been known to happen. One way to deal with it might be to assign a DQ a time of 999 or some other ridiculously large number (most runs only take between 20 to 90 seconds). It'd make the results sheet look more messy, but it seems like it'd work, right?

 

[Peter_SSs]

You still didn't say what result you actually want if someone only had one valid time or if they had no valid times.
So I'll take a guess and perhaps you can clarify if this is not suitable.

Each formula copied down.

Excel Workbook
	A	B	C	D	E	F	G
1	Time 1	Time 2	Time 3	Time 4		Fastest Two	Position
2	DQ	1:55	2:50	DQ		4:45	1
3	3:30	3:30	DQ	3:29		6:59	3
4	DQ	1:32	DQ	DQ		No Result	-
5	DQ	DQ	DQ	DQ		No Result	-
6	2:32	3:10	2:20	3:01		4:52	2
Fastest Times

 

[JBH123]

Oops, I guess I didn't, did I? My apologies. To your credit, you did guess correctly, i.e. if a racer does not have at least two valid runs, he/she does not get ranked with those who do.***

So, your solution looks like it will work. Thank you again!

Is there any way to rank those who had only one valid run based on their single fastest time, and to place all of those people immediately after those who had two or more valid runs? And then to place all the "no-timers" at the very end (ideally as equal placings)? In other words, the slowest "two-timer" should still finish ahead of everyone who only had one valid time (even if that one valid time was faster than one or both of the two-timer's runs) - and the slowest "one-timer" should finish ahead of anyone who didn't have any valid runs at all. I've tried, but I think it broke my brain.

 

[Peter_SSs]

Is this format any use?

Excel Workbook
	A	B	C	D	E	F	G
1	Time 1	Time 2	Time 3	Time 4	Fastest Two	Fastest One	Position
2	DQ	1:55	2:50	DQ	4:45	-	1
3	3:30	3:30	DQ	3:29	6:59	-	4
4	DQ	1:32	DQ	DQ	-	1:32	7
5	DQ	DQ	DQ	DQ	-	-	8
6	2:32	3:10	2:20	3:01	4:52	-	2
7	1:28	DQ	DQ	DQ	-	1:28	5
8	2:31	DQ	2:26	2:26	4:52	-	2
9	DQ	DQ	DQ	DQ	-	-	8
10	DQ	DQ	1:28	DQ	-	1:28	5
11
Fastest Times

 

[JBH123]

That looks good! I will try it out in the next day or two when I get the time. Thank you again for your help.

 

[JBH123]

Sorry for another long delay. Your last example works well if there are no intervening columns, but what if there's one or more columns in between each time?

(In my case, those other columns contain additional conditions that may affect the final time. For example, there's a column that counts the number of cones a rider knocks over during that run, and the formula for the final "adjusted" time adds .10 for each cone hit.)

 

[JBH123]

Also, is there a particular need for the ROUND function in your example? Why not just use SUM on its own?

 

[Peter_SSs]

.. what if there's one or more columns in between each time?

I expect there is something that is in each of the headings of the columns of interest, that is not in the other headings. In my example below, that is that each of the green columns ends with the word "Final". If that is not the case for you, can you make it that way?

I know that I have put "rubbish" information into the blue cells, but that should not be relevant to the solution and I've deliberately left the green cell values the same as the values in post #14 as the results should be the same, just ignoring the new blue cell values..

Note that the column M formula is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

I have assumed that you are using Excel 2007 or later. If not, alternative formulas will be required as COUNTIFS will not work in earlier versions.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	T1	Cones	T1 Final	T2	Cones	T2 Final	T3	Cones	T3 Final	T4	Cones	T4 Final	Fastest Two	Fastest One	Position
2	2:30	3	DQ	2:30	3	1:55	2:30	3	2:50	2:30	3	DQ	4:45	-	1
3	2:30	3	3:30	2:30	3	3:30	2:30	3	DQ	2:30	3	3:29	6:59	-	4
4	2:30	3	DQ	2:30	3	1:32	2:30	3	DQ	2:30	3	DQ	-	1:32	7
5	2:30	3	DQ	2:30	3	DQ	2:30	3	DQ	2:30	3	DQ	-	-	8
6	2:30	3	2:32	2:30	3	3:10	2:30	3	2:20	2:30	3	3:01	4:52	-	2
7	2:30	3	1:28	2:30	3	DQ	2:30	3	DQ	2:30	3	DQ	-	1:28	5
8	2:30	3	2:31	2:30	3	DQ	2:30	3	2:26	2:30	3	2:26	4:52	-	2
9	2:30	3	DQ	2:30	3	DQ	2:30	3	DQ	2:30	3	DQ	-	-	8
10	2:30	3	DQ	2:30	3	DQ	2:30	3	1:28	2:30	3	DQ	-	1:28	5
Fastest Times

Also, is there a particular need for the ROUND function in your example? Why not just use SUM on its own?

Yes there is a need for ROUND.
Excel is not able to store or calculate everything exactly, there can be slight anomalies. Without the round, I found, for example, a row that had the 2 fastest times of, say, 2:32 and 2:20 was not being ranked equally with another row that had the 2 fastest times of 2:20 and 2:32. The rounding ensures that they are ranked equally.

 

[SarahFloss]

I have a couple of further complications to add to this thread! I am trying to find the fastest of up to three times (see blue cells below) which are not in adjacent cells. Furthermore, the times themselves are the result of a calculation such as this example: =TEXT((L5-M5)/2,"h:mm:ss" and because they are counted as TEXT due to the time format, I cannot seem to get the MAX formula to work. I need to use the fastest time of the three for further calculations and as this is just a small sample of the full database, I'd rather not input them all manually. Hope that this all makes sense and thank you in advance!

[/URL][/IMG]

 

[shg]

I don't understand the "not adjacent" part, but instead of the TEXT function, use =(L5-M5)/2 and format as "h:mm:ss". Then you have numbers.