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

leighhobson89

New Member
Joined
Aug 25, 2016
Messages
33
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.
 

Attachments

  • 2021-03-16_20h56_03.png
    2021-03-16_20h56_03.png
    37.8 KB · Views: 5
  • 2021-03-16_21h08_22.png
    2021-03-16_21h08_22.png
    37.6 KB · Views: 4

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
575
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHI
1
2Input
3
4Time 1Time 2Time 3Time 4Time 42nd SmallestSmallestMin = Smallest
50:17:100:10:050:08:200:05:260:02:270:08:200:05:260:05:26
6
Data Sheet
Cell Formulas
RangeFormula
G5G5=SMALL($A$5:$D$5,2)
H5H5=SMALL($A$5:$D$5,1)
I5I5=MIN($A$5:$D$5)
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,592
Messages
5,637,290
Members
416,962
Latest member
samfuge

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
Top