Custom charting - Stacked charting with a line

  • Thread starter Thread starter Randy Lefferts
  • Start date Start date
R

Randy Lefferts

This may be difficult to explain but am going to try :)

I need to track "Liaison Costs" and "Sorting Costs" by
month. I also need to compare "Liaison Costs" to a
budget and generate the difference on the chart for the
given month, at the same time displaying a stacked bar
that shows the "Liaison Costs" and "Sorting Costs".

For example:

Jan Feb Mar
Liaison Costs 15,000 16,000 9,000
Sorting Costs 6,000 15,000 11,000

Budget 15,000 15,000 15,000

So now I need to plot the charges for sorting and
liaisons as a stacked bar. I also need to compare the
liaison costs vs the budget and display the difference.

I can display it so that the liaison costs are on the
bottom of the stack bar and then draw a line across the
months to represent the budget of 15000. The part I am
stumped on is whether it would be possible to now display
the difference along the "line" that represents the
budget (or anywhere else on the chart for that matter).

Is this possible and if so, any direction is most
appreciated!
 
Do you want to display a Chart Label with the difference, or another series
that represents the difference?
 
Preferably, I would like to see the difference displayed
at the top of the bottom half of the stack bar. So
with "Sorting Costs" the top bar and the "Liaison Costs"
as the bottom bar, I would like to see the difference at
the top (or thereabouts) of the "Liaison Costs" bar,
which will be the bottom half of the stack bar.

Considering that we will be dealing in units of 1000's,
the difference between the budget and actual Liaison
Costs is going to be "mostly" in the "100's", in either
direction. Plotting them as an independent series would
sort of have them "lost" as the numbers would place them
at the bottom of the list. However, if this could have a
separate axis that could be "set" so that the budget
(around 16k) would be around the 0 mark in the difference
series, that could work. Since it would plot the
difference around the baseline of 16k, it would position
the labels at about exactly the area I need it to.

I do know that I can manually just add text boxes and
plug in the values on a monthly basis but if this could
be automated it would make it much more likely to have
less errors from the end user.

Hopefully this isn't too confusing.

Thank you for your reply :)
 
Randy -

Stacking Liaison and Sorting is going to complicate things, so I'll describe how I'd
do just the liaison/budget piece of it.

I'd have actual liaison values in column B and budget in column C. Column D would
use formulas that insert the minimum of B and C. Column E would contain formulas
that show the minimum of 0 (zero) or C minus B (and therefore it fills from the
actual liaison amount up to the budget, and is a negative variance). Column F has
the minimum of 0 or B minus C (so it shows how much the actual liaison exceeded the
budget). I'd make a stacked column chart with column D in blue, column E in green
(i.e., unspent budget), and column F in red (overspent). I'd leave off the line
series for budget.

The sorting values complicate things. If your liaison actuals were >= the budget,
you could simply stack it on top, and there's no misunderstanding. If your liaison
actuals are < budget, do you decrease the sorting values by the amount not spent, so
the total is accurate? How does the reader know that sorting includes this reduced
amount plus the unspent amount?

You can address this by using two charts, one showing just the liaison/budget, as I
described above, then the other showing just the liaison/sorting values.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top