Floating Bars

[Michael beuhler]

I have two questions about the Floating Bars Chart, which
is the only way I can visualize when people show up for
and then leave for their shift.

1) Can I have the color of the bar for a particular entry
be dependent upon a value in the spreadsheet. Yes, I know
you can change the color of one bar to another color (or
all of them), but can you key the color you have chosen to
a value in the sheet (like say 1=red, etc.)

2) Can I combine two floating bar charts without the real
difficult, ugly, messy job of stretching one with no
background over the other and trying to make them line up
(they don't).

ANY help with EITHER question will save me many hours of
work and trouble, especially later on!

Thanks in advance,
Michael Beuhler
(e-mail address removed)

 

[LeninVMS]

Michael,

1) You can change the colour of a bar dependent on value, but you woul
need to use VB for that. A simple solution for your problem if you hav
a defined set of categories for classification is given below (assume
two categories):

Next to the column start time, add columns category 1 and category 2
followed by ShiftDuration and enter formulae like the following

category 1: If( check condition for category 1, ShiftDuration, 0)
category 2: If( check condition for category 2, ShiftDuration,0)

Use a 3-D stacked bar chart to plot columns employee, start time
category 1 and category 2.

Click on start time series and set patterns and lines to none.

Refer Conditional Charts o
http://peltiertech.com/Excel/Charts/format.html

2) Refer

'Jon Peltier's Align Charts
(http://peltiertech.com/Excel/Charts/chartvba.html#AlignCharts

 

[michael beuhler]

Much thanks for the references!

With respect to question #1 (Can you change the Floating
Bar colors of the Floating bar charts to reflect data in
the spreadsheet) you refer to 3d stacked chart, which has
the values vertical, not horizontal (as they need it to
be, and they need that "floating" position). Is the only
answer to use visual basic to change the colors on
Floating Bar charts? The decision basis would be a simple
1=red, 2=blue, 3=yellow

Thanks again
Michael

 

[Jon Peltier]

Michael -

My web page uses a column chart for example, but you can do exactly the
same if you make a bar chart. To float them, use one unchanging series,
which you make invisible, as the first series. The changing series stack
on this one, although of course only one has nonzero values.

- Jon