Negative values in stacked columns

  • Thread starter Thread starter Bernard Liengme
  • Start date Start date
B

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.
 
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.
 
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 C1:D1 enter 'Adjusted Year' and Duration respectively.
In C2 enter =IF(A2<0,A2+B2,A2)
In D2 enter =IF(A2<0,-B2,B2)

Copy C2:D2 to rows 3:whatever.

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

Use C:D 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
 
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
 
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.
 
Back
Top