Background
I am trying to produce a table of values for the positioning of the hands of a clock at any time, limited by one second intervals, to demonstrate the feasibility of an ultra precise, digitally rendered analogue clock.
One integer unit is 1 number on the face of the clock, i.e. 1 through 12 arranged in the classical way of a circle with equal, 30 degree angles between numbers.
The hour hand ticks 1 integer unit every hour, in increments of 1/3600ths
The minute hand ticks 1 integer unit every 5 minutes, in increments of 1/300ths
The second hand ticks 1 integer unit every 5 seconds, in increments of 1/5ths
Thus at 01:05:05, the hands approximately overlap, but I need to know the uncertainty.
The Problem
Is there a way to calculate, using excel, the relative difference between the 3 values in a way that I can then sort the data to find the positions where the hands are closest to overlapping (to then analyse the uncertainty)?
I do not wish to know where the hands cross, my data already tells me that much.
To make things a little clearer, here is my data.
I want to find out how close each second row is to overlapping, relative to every other row.
Such as:-
00:00:00 would be a perfect overlap, call the result of this 0.
01:05:05 is a very close overlap, call the result 0.005 relative to 00:00:00.
03:45:30 is nowhere near an overlap, call that result 0.99 relative to 00:00:00.
Ideally '0' is a perfect overlap. '1' is the point at which each hand is equidistant from the others.
I want to quantify the fact that the overlap of the 01:05:05 hands is far closer than the 01:04:55 value in the table for instance.
It is clear to see, due to the values at 01:05:05 being all very close to 1, whereas the values at 01:04:55 have a larger difference between the hour/minute and the second hands, and so are not as close to overlapping.
Thanks in advance