Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Randy Lefferts
 
Posts: n/a
Default 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!
  #2   Report Post  
Barb Reinhardt
 
Posts: n/a
Default

Do you want to display a Chart Label with the difference, or another series
that represents the difference?

"Randy Lefferts" wrote in message
...
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!



  #3   Report Post  
 
Posts: n/a
Default

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"

wrote in message
...
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!



.

  #4   Report Post  
Jon Peltier
 
Posts: n/a
Default

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

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"


wrote in message

...

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!



.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
add line to a stacked area chart already created Mitch Charts and Charting in Excel 3 April 3rd 23 04:43 PM
How do I combine a line and a percentage stacked column chart? Liza Charts and Charting in Excel 1 February 3rd 05 02:40 PM
Line chart - date line association gone mad! Johannes Czernin Charts and Charting in Excel 5 January 17th 05 09:48 PM
Macro - Skipping a line PaulPoll Excel Worksheet Functions 4 December 21st 04 01:43 AM
pivot table multi line chart souris Charts and Charting in Excel 2 December 7th 04 04:56 AM


All times are GMT +1. The time now is 02:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"