Nazar Sigaher's Frame clock in Excel

posted Aug 22, 2013, 6:04 AM by Krisztina Szabó   [ updated Aug 22, 2013, 6:06 AM by r ]
Dr. Frederick Frankenstein: No no no no no. No no no no no no no. No no no no no no no no no. I'm not a Frankenstein. I'm not a Frankenstein. I'm a Fronkensteen. Don't give me that. I don't believe in fate, and I wont say it. Alright, you win, you win, I'll give. I'll say it, I'll say it, I'll say it. Destiny, destiny, no escaping that's for me. Destiny, destiny, no escaping that's for me. Destiny, destiny, no escaping that's for

Inga: Dr. Fronkensteen, wake up [...]

by The FrankensTeam

Inspired by the designer Nazar Sigaher's work:

Nazar Sigaher->Design->Frame

There is no question - only an answer: It could be done in Excel.

Here is a short video presenting how it works in Excel 2013 - with the smoothing effect:

(Interestingly, the smoothing effect works only with user interaction. If we fire the calculation from VBA, it is not smoothed. So we used Application.Sendkeys “{F9}” instead of Calculate when making the video… but it is another topic.)

As you can see, we changed the original concept a little bit and added the handle for seconds too. Then we thought it could be more challenging if the user can choose how many handles he wants to see.

The base of the clock is an xy scatter chart, and the whole construct was built using only one data series  Each of the handles is drawn using 9 data points:
  • starting point is the middle of the clock (the center)
  • the second point is the end of the handle, the midpoint of a side of the square
  • third to sixth 4 points stand  for the four corners
  • the 7th point is the side-midpoint again
  • one point goes back to the center
  • and the last one goes back to the starting point
Then can start the next handle.

Because of the circular movements, it is obvious that we have to use the angles, and than sin and cos functions to calculate the x and y coordinates. It is not difficult to convert hours, minutes and seconds to angles, or in other words, to determine the degrees of the handles. As you can see on the picture below, the degree of the handle determines the midpoint of the side (31°). To calculate the degrees for the corners, we only need to add 45°, 135°, 225°, 315° to the degree of the handle.

To have a small space between the two lines of the handle, we use a few degree shift between the midpoints, and also for the points in the center. This is the trigonometric base of the formulas, which you can see in the help-formula named arr_1.
There is another interesting trick. We put the coordinates of the three handles in one formula resulting a 2D array. The above mentioned arr_1 contains 9 columns and as many rows as the number of handles. We use CHOOSE formula to build up this array.

After calculating SIN and COS values based on the degrees provided by arr_1, the final named formulas (arr_x and arr_y) are used on the chart as coordinates.
The dark-gray background of the chart is drawn by a bar.

If you are interested in more technical details, please download the Excel file where you can analyze the formulas.

You can find more Excel-clocks here:
Analog clock
Bubble Clock
QLOCKTWO con Excel