Using Time as axis, and displaying info on plots

[dwn4crwn]

Hi every one...i've been racking my brain out on this one.

I'd like to come up with a way to display things better.

Background: I am making a running worksheet that has events fo
multiple days. each day has a varying number of events. In order t
effectively display the data for my boss, the best way is to do it on
24 hour clock instead of the standard 12 hour clock. This allows us t
see patterns occuring at certain times.

The way I have it currently set up is converting times to angles an
using the days as magnitudes. Basically doing a polar chart t
cartesian conversion. This allows the data to be plotted in circle
with each day having its own circle. I use the x-y plot to do this.

Problem#1

I would like to do one of two things. 1) place my mouse pointer ove
the plot and instead of the coordinates showing, the event is shown.
2) the event itself is labeling the points (and if possible whe
placing the mouse pointer over the point, extra information about th
event is displayed)

the second course is desirable, but that may be a little to much fo
excel.

Problem#2
does anyone know if I can display a clock face with 24 hours as a
axis? I'm not sure if this can be done but if it can it would save m
some time on Powerpoint.

Thanks for any help

 

[Jon Peltier]

Your first problem may be satisfies using Tushar Mehta's Chart Hover
Label utility, a free Excel add-in from http://tushar-mehta.com.

I'm not sure what you mean by the second question. You can make a radar
chart with X values of 24,1,2,3,...,22,23 which has the numbers starting
with 24 at the top, 6 straight to the right, 12 down, and 18 to the
left. You can only plot values for each series at the integer values
corresponding to the X values above.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[dwn4crwn]

Jon

you are the man. thank you for helping solve the first problem.

as for the second, let me clarify. I would like to plot on the same
graph as the first one the 24hr clock so we could see more clearly what
is going on each hour. It would give us a better visual by hour.

you don't know how to link an access database to excel would you???
thats kind of where we are holding all the information.

 

[Tushar Mehta]

That's a very interesting way of documenting notable events!

Why not just take a photo of a clock and position it on the chart
centered at (0,0). I just tried it using a 12 hour clock and it looks
nice. Of course, you will have to find a picture of a not so common
clock with a 24 hour face.

Alternatively, add an inner (or outer) 'circle' consisting of 24 equi-
angular points. Label this series with the numbers 0, 1,...,23 (center
the labels). Hide the marker and the line.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tushar Mehta]

This might be even better than the previous suggestion...

Create a outer circle with spokes!

Suppose the numbers 0,1,...,24 are in A1:A25.

Enter in
B1: =A1/24*2*PI()
C1: =COS(B1)
D1: =SIN(B1)

Copy B11 down to 2:25.

Enter in C2626 the value 0 (i.e., zero).

Add the following named formulas (Insert | Names > Define...):
Vals24 =ROW(Sheet3!$1:$24)
Vals48 =ROW(Sheet3!$1:$48)

OuterR =3

OuterX =OuterR*COS((Vals24-1)/24*2*PI())
OuterY =OuterR*SIN((Vals24-1)/24*2*PI())

SpokesX
=OuterR*N(OFFSET(Sheet3!$C$1,IF(MOD(Vals48,2)=0,Vals48/2-1,25),0,1,1))
SpokesY
=OuterR*N(OFFSET(Sheet3!$C$1,IF(MOD(Vals48,2)=0,Vals48/2-1,25),1,1,1))

[Note that Sheet3 refers to the sheet I used for testing.]

OuterR defines the radius of the outer circle. Change it as desired.

[The above can definitely be cleaned up to reduce the interplay between
the named formulas and the sheet data; but I layered this solution on
my last post and am being lazy...]

Create 2 new series in the chart.

The first uses OuterX and OuterY as the x and y values. For more on
how to use named formulas in charts see Names in Charts
(http://www.tushar-
mehta.com/excel/newsgroups/dynamic_charts/names_in_charts.html) Use
either XY Chartlabeler (www.appspro.com) or Chart Tools (www.j-
walk.com) to add A1:A24 as centered labels. Set the series marker and
line to none. Alternatively, set the line to a 'smoothed line' to
create a visible circle.

The 2nd series uses the SpokesX and SpokesY formulas to create
'spokes' on the chart. Neat, eh? Format this series with a light gray
line (ensure that 'smoothed line' is *not* selected) and no marker.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[dwn4crwn]

Tushar Mehta

man...you guys are ridiculously smart with this whole excel thing.
Thank you very much. the info helped greatly.

The only problem I seem to have with it is the data labeler and th
hover add-ins seem to effect each other when the hover option is turne
on. I'd like to keep the clock times locked in and have the option o
being able to move my mouse over the various points.

Its not a huge issue but an aesthetic one. Again...you gave advice i
the nick of time. you are the man..