special graph format

[ikke thuis]

I need a graph type that's not standard in Excel.

suppose:
X axis =time in days
Y axis represent e.g. a production qty.
Each day on Xaxis has a target value T and a realized qty Q.
Q can be <T or =T or >T.
So the graph will show 2 lines.
If T-line > Q-line surface between Q and T must be red
If T-line < Q-line surface between Q and T must be red
For visual management it creates a graph; if you see surface green about
same size as surface green everage will be on target till that date.

Problem: I cannot find that type of graph in the excel graphs.
Does anyone know how to get it?

Huub

 

[ker_01]

I'm not aware of a line or area graph that will do what you want (but I'll
track this post so I can learn from any responses from the graph experts).

Here is an alternative:
Create 3 series of data:
1. your target minus actual, if actual is lower (=min(target,actual))
2. the larger of zero or (target-actual) (=max(0,(target-actual)))
3. the larger of zero or (actual-target) (=max(0,(actual-target)))

Then create a stacked column chart. You can leave the columns as is, but
also try the setting Options: Gap Width = 0 and see if you like the look of
having the columns together. I think that if you include a goal line (below)
that the chart is much easier to read with the columns at the default gap of
150, but if you are trying to mimic an area graph, maybe you will like the
zero setting better.

Change series 1 (the bottom of the stack): Format data series: Patterns: No
Fill and No Border (shows as clear)
Change series 2 Pattern/Fill to red
Change series 3 Pattern/Fill to green

If you really want to show your goal line as linear (easier to see than
trying to track the tops and bottoms of red and green sections), copy the raw
target data series and paste it onto the graph (copy the data range normally,
then right-click the graph and select paste), then right click the new series
on the graph, and change graph type to line. You might also want to make the
line heavier, so it sticks out visually.

HTH,
Keith

 

[ikke thuis]

Thx for your answer.
Very clever, yes I see what you mean.
width 0 is better indeed.
The only problem I have is that I want to see the actual line better.
The way you describe it there will be an extra line in the graph and because
columns have horizontal lines, the new line is a bit confusing. So I don't
use that extra line. Creating a thin raster in background increases the
results but in fact I want the bottom of red section and top of green
section a thicker line. The basic idea is simple and good.
Thx
Huub

 

[ikke thuis]

I did some experiments but you're complete right.
The best results gives an extra targetline, bit heavier than normal.
As long as target doesn't change to much per day, the result is perfect.
Thx again
Huub