Custom charting - Stacked charting with a line

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?

"Randy Lefferts" <[email protected]> wrote in message
news:[email protected]...
> 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!

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" <[email protected]>
wrote in message
>news:[email protected]...
>> 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!
>
>
>.
>

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

[email protected] 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" <[email protected]>
>
> wrote in message
>
>>news:[email protected]...
>>
>>>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!
>>
>>
>>.
>>