Archive of Mr Excel Message Board

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

: 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

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

:

{"","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

: