Re: Custom charting - Stacked charting with a line

From: Jon Peltier (jonREMOVExlmvp_at_peltierCAPStech.com)
Date: 03/03/05 

Date: Wed, 02 Mar 2005 22:10:36 -0500

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

anonymous@discussions.microsoft.com wrote:

> 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 :)
>
>
>>-----Original Message-----
>>Do you want to display a Chart Label with the
>
> difference, or another series
>
>>that represents the difference?
>>
>>"Randy Lefferts" <anonymous@discussions.microsoft.com>
>
> wrote in message
>
>>news:365701c51f46$c5a083d0$a401280a@phx.gbl...
>>
>>>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!
>>
>>
>>.
>>