# Stacked bar chart dilemna

#### venrii

##### New Member
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

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Aaron

##### Board Regular
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
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
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

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
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

Mark W: Nice one.

#### venrii

##### New Member
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
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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,040
Messages
5,856,987
Members
431,844
Latest member
JasonNH

### 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.

### Which adblocker are you using?

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

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