Something strange happening while switching from xlBar to xlStacke

[SoftwareTester]

While changing from xlBar into xlStackedBar the chart changes very strange.
If I change back all is OK again.

Task Start Finish
Task 1 13-Oct-09 17-Nov-09
Task 2 10-Nov-09 10-Mar-10
Task 3 01-Oct-09 01-Oct-09

I select all 3 columns and create a Bar chart from the menu. All OK
(for each task 2 bars appear, 'finish' being longer)

If I now change the charttype into StackedBar
the (each !) 'Start' bar will occupy the same size like the (each !)
'Finish' bar and will be visibible right in front of (or 'covering half of
the ) 'Finish' bar.

Any idea why it happens and how I can correct it

 

[Barb Reinhardt]

Is it possible that one of the bars is on the secondary axis?

HTH,
Barb Reinhardt

 

[SoftwareTester]

No, that's not the problem

It's very easy to reproduce it (and I did on several PCs).
Please have a look and you'll see

 

[BSc Chem Eng Rick]

Hi

SoftwareTester, what you get when you change to stacked is correct, remember
a stacked bar basically takes the reference as "Zero" then plots the first
number (say 4000 in one colour), it then adds the next number to 4000 (say
2000) and plots from 4000 to 6000 in a different colour. If you change your
dates to numbers and add them together you will see what I'm saying.

What you need to do is plot the start Date and then the DIFFERENCE between
the start and finishing dates. Try this as your table,

Task Start Finish
Task1 13-Oct-09 35
Task2 10-Nov-09 120
Task3 01-Oct-09 0

If this helps please click "Yes".
Rick

 

[Bob I]

This happens due to the data being invalid for that type of chart.

You have created bars with the right end being set by your single data
point which is a Date, and specified no start date or Zero if you will.
Excel wizard is then left to make that choice, and the "Zero" will be
different due to the effort to create a presentable chart from invalid data.

 

[SoftwareTester]

I know that if I use Duration instead of Finish I can create a stackedBar
correctly, but I do have Finish available and not Duration. Of course I can
add an extra column but I want to avoid that if possible.

I don't understand WHY xlBar uses the same values (days) to start drawing
(and draws 2 bars below each other) and xlStackedBar will not. Even if I
adjust the Scale of the Finish series it will not display correctly and use
secondary axis.
So basically it comes down to "how can I make Finish use primary axis as well"

I realy want to use Start AND Finish using primary axis for both as I want
to use secondary axis for something else.

Any ideas?

 

[BSc Chem Eng Rick]

OK Cool, now I understand what you're trying to do. Plot your bar chart as
you have always done. Now right click on your start series --> "Format Data
Series" Under the "Series Options" set "Series Overlap" to 100% Overlapped.
You should see the series on top of each other.

Now "Chart Tools" --> "Design" --> "Select Data" and change the order of
your series' so that Start comes after Finish.

If this helps please click "Yes"
<><><><><><><><><><>

 

[SoftwareTester]

Thanks for the idea, but it doesn't work completely.
If I do like you explain I end up with STARTseries covering first part of
FINISHseries (so the remaining of the FINISHseries actually are DURATION like
I intend) but the STARTseries all start at the axis unfortunally.
In order to solve that I tried setting border and patterncolor of the
STARTseries to xlnone. Doing that will hide the STARTseries but show the full
FINISHseries (starting at the axis).
Next I tried setting border to xlnone and patterncolor to the color of the
plotarea. Indeed now I can't see the STARTseries anymore (also not on paper!)
and I do see the DURATION (being remainder of FINISHseries). But there is one
problem : I don't see gridlines where they "overlap" STARTseries. This
doesn't look nice. If gridlines would be drawn AFTER drawing the bars it
would be OK but thy aren't.

Any solution ?

 

[BSc Chem Eng Rick]

The only way to accomplish what you are trying to do is to plot an xlstacked
bar as we discussed earlier where you use the start date and the duration. I
know this means you need to add a column and do a calc but it simplifies what
you want to accomplish. Once you've done this, set the fill of the "start"
series to "No fill". Now you only see the duration and you still see the
gridlines.

If this helps please click "Yes"
<><><><><><><><><><>

 

[Jon Peltier]

What's strange? Your first task has values of 40099 and 40134, so when
you stack them, you get a combined length of 80233.

If you want to show one bar from the left edge of the chart to 11-Oct-09
and another stacked on it that stretches from 11-Oct-09 to 17-Nov-09,
you have to calculate the difference (duration) and plot this calculated
Duration data, not the Finish data.

- Jon