Microsoft Office Tutorials and References

In Depth Information

This example, named plot circles.xlsx, is available at this book's website.

Creating a clock chart

Figure 17-20 shows an XY chart formatted to look like a clock. It not only looks like a clock but it also func-

tions like a clock. There is really no reason why anyone would need to display a clock such as this on a work-

sheet, but creating the workbook was challenging, and you may find it instructive.

Figure 17-20:
This fully functional clock is actually an XY chart in disguise.

The chart uses four data series: one for the hour hand, one for the minute hand, one for the second hand, and

one for the numbers. The last data series draws a circle with 12 points (but no line). The numbers consist of

manually entered data labels.

The formulas listed in Table 17-2 use basic trigonometry to calculate the data series for the clock hands. (The

range G4:L4 contains zero values, not formulas.)

Table 17-2: Formulas Used to Generate a Clock Chart

Cell

Description

Formula

G5

Origin of hour hand

=0.5*SIN((HOUR(NOW())+(MINUTE(NOW())/60))*(2*PI()/12))

H5

End of hour hand

=0.5*COS((HOUR(NOW())+(MINUTE(NOW())/60))*(2*PI()/12))

Origin of minute

hand

=0.8*SIN((MINUTE(NOW())+(SECOND(NOW())/60))*(2*PI()/

60))

I5