Stacked Area Charts

[Guest]

I want to use a stacked area chart, but some of my data (intentionally) uses negative numbers. These display in okay in most area charts, EXCEPT stacked. In the stacked version the negative numbers are displayed as positive

This seems like a bug to me

Anyone know a way around this

McB

 

[Jon Peltier]

McB -

How do you expect the stacked area chart to show a negative value?
Currently it draws the negative point below the lower series' higher
point. If you trace the data on the chart, you'll see that the lines
connecting the first series' points and the second series' points are
consistent. Probably Excel doesn't know what to do with the negative
area. It still draws the area from the earlier series to the later
series, but it draws this area downward, covering up part of the earlier
area.

You might need to consider an alternate algorithm for what you're trying
to plot. If you describe it, someone might have an idea.

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

 

[Guest]

Thanks for that Jon

I still think this is inconsistent behaviour. If I plot a graph with some negative values using a stacked column, the negative values "hang" below the X axis. This is the effect I want with a stacked area chart too. I have been cheating in the past by removing any overlap between columns, making it look like a stacked area, but I'd like to do this directly

I need it to represent the tax system in Australia. More specifically, I need the negative values to show up as offsetting the normal tax take. This is how I represent rebates or tax credits. Doing them as area or stacked column allows the user to see what components of the system are operating over what income ranges. Stacked area would give a neater result

Hope that makes sense

Regard

David "McB"

 

[Jon Peltier]

David -

Actually, I think the stacked area is more consistent than the stacked
bar in one sense. The stacked bar shows the total of all the bars if
every point is positive. If there are negative ones hanging below the
axis, the total is somewhere in between the maximum and minimum, but you
have to somehow subtract in your head to see what the net is. Add a line
chart series with the sums, and you see that it doesn't follow a logical
pattern of the stacked bars. If there are any zeros: it's in between the
max and min of all the bars. Hanging the zeros below the axis somehow
dissociates the negatives from the positives.

If you trace the top of the uppermost stacked area series, it follows
the total. It dips below the prior one if it has a negative, and this
criss-cross isn't clear. A line chart series of the sums added to the
chart follows this series exactly.

I don't care for either chart type, especially with negative data mixed
in, but I think the stacked area combined with a line series of the
total is the better chart type to use for this.

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