Stacked bar chart dilemna

[venrii]

Here's my dilemna. I have the following type of data

108 108
107 115
120 128
150 140
130 85

Is there a way in Excel to create a stacked chart that will display both values, up to the max of the two rather than the total?

Thanks,

Venrii
This message was edited by venrii on 2002-10-28 13:17

 

[Aaron]

venrii
I think what you are looking for is already posted. If you look under author Aladin you should be able to find an array formula that will help. If it is not under his name check under one of the moderators names. If you don't find anything let me know. I think I have an old spreadsheet around that has the array formula. It would take me longer to find it I think than you could find the formula on the board. thx

 

[Alriemer]

Venrii,

Another approach is to have the "bottom" data series be the smaller of the two values; have the "top" series be the difference. ie, in your second example, you would have one value as 107 and one value as 8, making the total height of the stacked bar 115. Is this what you were looking for?

Hope this helps.
Alriemer

 

[venrii]

On 2002-10-28 14:44, Alriemer wrote:
Venrii,

Another approach is to have the "bottom" data series be the smaller of the two values; have the "top" series be the difference. ie, in your second example, you would have one value as 107 and one value as 8, making the total height of the stacked bar 115. Is this what you were looking for?

Hope this helps.
Alriemer

That works most of the time, but for the last cases you result in a negative number if you do the simple subtraction. So you are forced to manually substitute the values, keep track of which ones you changed, and then update the chart since the data series will imply the smaller value goes with the first series when it really belongs to the second series.

 

[Mark W.]

Use an ordinary Column Chart type and set the Overlap to 100. See the Format | Selected Data Series... menu command's Options tab.

 

[Alriemer]

venrii,

Understand concern regarding last case. Assuming you have 130 and 85 in cells A1 and B1, enter =MIN(A1:B1) in cell C1 and =MAX(A1:B1)-C1 in cell D1. Let column C and column D be your data series. But you are right, the larger value will always appear to be "on top," forcing manual formatting. Two approaches:
1. Use a side-by-side rather than a stacked bar graph (not optimal)
2. Use a stacked bar graph with 4 data series - see this link for more ideas, or we can go through it.
http://216.92.17.166/board/viewtopic.php?topic=26109&forum=2

Hope this helps,
Alriemer

 

[Alriemer]

Mark W: Nice one.

 

[venrii]

I must be doing something wrong with Mark's solution because I still have the problem of a larger value covering the smaller value...

 

[Mark W.]

You can't have it both ways... you either create separate series of MINs and MAXes or live with the fact that the smaller values listed above may be eclipsed by the larger.