Best Approach to create a column to display the comparison between current lap time and previous lap time?

[leighhobson89]

Hello guys,

I have been trying for a few days to come up with the best approach to creating a column that will follow these rules, as yet without success:

- It will read in a previously calculated cell *let us call it F41 for later reference* that has worked out the previous best lap time, formatted as a duration "hh:mm:ss.xxx" where x is the split seconds.
- It will read in another hard coded cell that the user enters for the current lap time, formatted in the same way as above.
- It will perform a comparison between the two times with the objective of writing out the difference between the previous best and the value entered as the current lap, formatted as in the cells above with a trailing "+ or -" thus:
- It will write the difference in times as for example "-00:00:13.234" if the current time is faster than the previous best, and for example "+00:00:13.234" if the current time is slower than the previous best.
- In the event that the current lap entered by the user is better than the previous best F41, this will be the value now displayed in F41, so that when new times are added in the future, they are compared with this value as the previous best, but without this particular calculation in our new best column being affected by those future calculations.

Notes
1) I have discovered already by my previous approach, that although entering a slower time for the current time works fine with a simple subtraction formula, entering a lower time than the previous best this way does not work, because entering a lower time instantly updates the previous best new time as soon as it is entered, due to the way the formula picks the minimum from the range. This has the undesired effect of making the comparison column reflect "00:00:00.000" when it calculates the difference, as they obviously now match. I am therefore trying to avoid this conundrum in the approach I try next.
2) I would prefer to use hidden cells if needed and formulae to approach this, rather than running any macros, or adding any buttons.
3) The two attached screenshots show my previous attempt. The green one is the sheet the user will be reading, where it reads in this case the Z column that the user has entered, and compares it with the previous best, in F41 to J41 depending which waypoint we are concerned with (shown in the white table which will be hidden from the user eventually). You can see that the slower times are correctly displaying, and the faster time is not for the reason explained in note 1.

Thanks very much for reading and I really really hope someone can help. Thank you in advance.

 

[Alex Blakenburg]

I am not fully across how your spreadsheet works but have you considered the Small function ?
You could have a cell to store the Min and another to store 2nd smallest which you use or not use based on your if statement.

20210317 Best Time 2nd Smallest.xlsx
	A	B	C	D	E	F	G	H	I
1
2	Input
3
4	Time 1	Time 2	Time 3	Time 4	Time 4		2nd Smallest	Smallest	Min = Smallest
5	0:17:10	0:10:05	0:08:20	0:05:26	0:02:27		0:08:20	0:05:26	0:05:26
6
Data Sheet

Cell Formulas
Range	Formula
G5	G5	=SMALL($A$5:$D$5,2)
H5	H5	=SMALL($A$5:$D$5,1)
I5	I5	=MIN($A$5:$D$5)

 

[leighhobson89]

Thankyou, I was able to solve my problem with this function. Great help!

 

[Alex Blakenburg]

Thanks for letting me know, glad it was helpful.