Stacked bar chart dilemna

venrii

New Member
Joined
Oct 27, 2002
Messages
3
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Aaron

Board Regular
Joined
Feb 20, 2002
Messages
237
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

Board Regular
Joined
Aug 18, 2002
Messages
102
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

New Member
Joined
Oct 27, 2002
Messages
3
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

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

Alriemer

Board Regular
Joined
Aug 18, 2002
Messages
102
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

Board Regular
Joined
Aug 18, 2002
Messages
102

ADVERTISEMENT

Mark W: Nice one.
 

venrii

New Member
Joined
Oct 27, 2002
Messages
3
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top