Negative values in stacked columns

[Bernard Liengme]

It can be done but is a bit complex. The method relies on the fact that a
single data point can be given colour & border that differ from other
members of the data series. The problem is that you have to hide (no colour,
no border) the "Start" data series, and this makes it hard to grab a single
data point.
1) Make the chart, hide the Start series
2) Now we locate the hidden series. Click on chart and use Up/Down arrows un
till Start series is selected - you see square dots on chart and the series
is named in the Name box.
3) Use the left/right arrows to select one of the single data point with
negative values. Use menu item Format - the first item should be Selected
Data Item. Make the colour the same as your End series.

Email me (private address) if you want a sample file.

 

[Dave R.]

You have "post dated" your post, causing it to show up as the most recent,
constantly. Please refrain from that in the future, I believe it is frowned
upon, or some people will simply delete it.

 

[Tushar Mehta]

Yes, there's a way to make it work. You have to realize that XL stacks
negative and positive numbers independent of each other. So, you have
to adjust any combination that includes one negative number (year before
BC) and one positive number (duration).

Suppose you have in A1:B4

Year Duration
-100 20
100 30
150 50

Then, in C11 enter 'Adjusted Year' and Duration respectively.
In C2 enter =IF(A2<0,A2+B2,A2)
In D2 enter =IF(A2<0,-B2,B2)

Copy C22 to rows 3:whatever.

The result should look like:
-80 -20
100 30
150 50

Use C in your chart.


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2004

 

[Jon Peltier]

Alex -

If the bar crosses the axis, you need to build it from two series, one for the positive part,
one for the negative. There are more details on this page, which refers to columns, but works
the same way for bars:

http://peltiertech.com/Excel/Charts/StackedColumnsAboveAndBelow.html

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

 

[Alex Kudrasev]

I am actually using Excel to create historical timelines. You can do this by
charting two series - start year (this is set to invisible) and duration
using stacked column chart.

This works fine - *until* one puts in a negative start year (year BC) .

Excel doesn't want to draw a negative column for series one!

Is there anything I can do? Or is this a limitation in Excel?

Thanks,

Alex Kudrasev

 

[Alex Kudrasev]

Tushar,

Excellent idea!

The only thing is that the chart won't do the right thing if you start
negative and end up positive - eg -100 for 200 duration, but I guess there's
not much you can do about that...

Cheers,

Alex Kudrasev.