MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Charting problem


Posted by Rod on December 10, 2001 9:16 PM

Hi.
I want to put together a bar chart that has 3 series's with one of those series sitting ontop of the other. The chart is showing budget info One series showing Budgeted, and the other 2 actual, and the other commited. The Commited and the Actual series should sit on top of each other on a bar chart

can any body help


Posted by Mark W. on December 11, 2001 7:58 AM

Stacked column charts can't be used in a combination
chart.

Posted by Bariloche on December 11, 2001 9:32 AM

Oh yes they can

Rod,

Its all in how you arrange your data. Let's see if I can describe this.

Basically each month has to have two columns associated with it, Budget and Actuals. Each one of those columns then has two rows of data. Under "budget" you have only one row with data in it. Under "actuals" you have data in both rows. The second represents your open commitment data.

Set up a dummy data table. Merge cells b1 and c1 and put in "January", do the same for d1 and e1 and f1 and g1 so that you have three months. In b2 and c2 enter "B" and "A" respectively. Do the same in d2 and e2 and f2 and g2. Enter your budget and actuals data in row 3. Enter your commitment data in the "actuals" columns in row 4.

Select your data and insert a "stacked column" chart. This should be what you're looking for.

If you have your data in a pivot table, you can create this style rather easily as a pivot chart. The only difference is with a pivot chart months would be done as row fields.


enjoy

Posted by Mark W. on December 11, 2001 12:04 PM

I stand by my original statement...

Your solution doesn't employ a combination chart --
one with 2 different chart types. Also, out of
curiosity I examined your recommended solution
and I couldn't get it to properly label the "2nd"
column in each of the monthly periods.

: Hi. : I want to put together a bar chart that has 3 series's with one of those series sitting ontop of the other. The chart is showing budget info One series showing Budgeted, and the other 2 actual, and the other commited. The Commited and the Actual series should sit on top of each other on a bar chart : can any body help

Posted by Bariloche on December 11, 2001 6:02 PM

Re: I stand by my original statement...

Mark,

My "Oh yes they can" refers (rather ambiguously) to the fact that a chart which solves Rod's problem is entirely possible. Perhaps I should have phrased it "Yes it can". Again in reference to Excel being able to provide a solution to the problem. Regarding the solution not being a "combination" chart: Rod doesn't mention anything about needing a "combination" chart. He just wants a chart that will compare his budget to the "sum" of his actuals and open commitments.

The solution is not a "combination" chart. Its a stacked bar chart where the "height" of one of the bars is zero.

Regarding the month label not being centered under the two bars: on my machine here at home (E97), it doesn't center. On my machine at work (E2000), it does. Perhaps this is an element of functionality that occurs because of the pivot chart capability in 2000. Since, as I mentioned, this style of chart is easily achievable using a pivot chart.


take care

Your solution doesn't employ a combination chart -- one with 2 different chart types. Also, out of curiosity I examined your recommended solution and I couldn't get it to properly label the "2nd" column in each of the monthly periods. : Rod, : Its all in how you arrange your data. Let's see if I can describe this. : Basically each month has to have two columns associated with it, Budget and Actuals. Each one of those columns then has two rows of data. Under "budget" you have only one row with data in it. Under "actuals" you have data in both rows. The second represents your open commitment data. : Set up a dummy data table. Merge cells b1 and c1 and put in "January", do the same for d1 and e1 and f1 and g1 so that you have three months. In b2 and c2 enter "B" and "A" respectively. Do the same in d2 and e2 and f2 and g2. Enter your budget and actuals data in row 3. Enter your commitment data in the "actuals" columns in row 4. : Select your data and insert a "stacked column" chart. This should be what you're looking for. : If you have your data in a pivot table, you can create this style rather easily as a pivot chart. The only difference is with a pivot chart months would be done as row fields. : : enjoy

:

Posted by Mark W. on December 12, 2001 11:49 AM

Here's my labelling issue with your approach...

The data...

{"","Jan","","Feb",""
;"","Budget","???","Budget","???"
;"Actual",30,10,55,25
;"Committed",0,20,0,19}

The resulting chart SERIES...

=SERIES(Sheet1!$A$3,Sheet1!$B$1:$E$2,Sheet1!$B$3:$E$3,1)
=SERIES(Sheet1!$A$4,Sheet1!$B$1:$E$2,Sheet1!$B$4:$E$4,2)

1. First, note that this approach produces only 2
series instead of the 3 requested.
2. The consequences of having only 3 series are...
(a) You can't assign a separate fill color to
the 'Budget' column.
(b) The legend consisting of "Committed" and "Actual"
leads one to associate "Actual" with 'Budget' when
that may not be appropriate.
3. Finally, the data model doesn't readily produce
a "natural" X-axis label (marked as "???" in my
sample data) for the Committed/Actual stacked
column.

Your solution doesn't employ a combination chart -- one with 2 different chart types. Also, out of curiosity I examined your recommended solution and I couldn't get it to properly label the "2nd" column in each of the monthly periods. : Rod, : Its all in how you arrange your data. Let's see if I can describe this. : Basically each month has to have two columns associated with it, Budget and Actuals. Each one of those columns then has two rows of data. Under "budget" you have only one row with data in it. Under "actuals" you have data in both rows. The second represents your open commitment data. : Set up a dummy data table. Merge cells b1 and c1 and put in "January", do the same for d1 and e1 and f1 and g1 so that you have three months. In b2 and c2 enter "B" and "A" respectively. Do the same in d2 and e2 and f2 and g2. Enter your budget and actuals data in row 3. Enter your commitment data in the "actuals" columns in row 4. : Select your data and insert a "stacked column" chart. This should be what you're looking for. : If you have your data in a pivot table, you can create this style rather easily as a pivot chart. The only difference is with a pivot chart months would be done as row fields. : : enjoy

: