Conditional Chartting

  • Thread starter Thread starter Os
  • Start date Start date
O

Os

I have created my own plot area, and I need to plot a
line chart on it without showing axes, gridlines, or plot
area. My plot area is highlighted red on the upper and
lower rows, then yellow on the following rows (up/down),
while the inner area is highlighted green. My avg values
(data series) is right above the plot area (5 rows green,
2 Yellow, and 2 red - plus 16 columns).
Or my second alternative to plot a column chart that will
show for example: 1.99 - 2.21 = green / 1.70 - 1.99 and
2.21 - 2.50 = yellow, and any value below or over the
last values = Red. Can anybody help?
Thanks.
 
Thanks Jon for the reply. But is not exactly what i'm
looking for. Like I wrote before, I already set up my
plot area and highlighted it the way I wanted. I only
need to show the curve line on the sheet, without showing
the gridlines, axes, etc. My data series is from only one
row. Any idea?

Thank you.
 
Oh, so you need to add the series that makes the line/curve. From the Chart menu,
select Source Data, click on the Series tab, click on Add, then select the values
for this series, add a name, etc.

If you are only providing Y values, Excel is just going to use counting numbers for
X (1, 2, 3, etc.). Maybe this is okay.

When you add the series, it will probably not be the type you want. Select the new
series and on the chart menu, select Chart Type, and choose an appropriate line or
scatter type.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Thanks Jon. That was my initail try before, but is not
helping my purpose. You see, I have a user enters data
every half an hour, and according to that data/values, we
need the curve line to visually show whether the product
is on the Green (pref), Yellow, or Red. But I don't know
how to highlight individual gridlines with different
color. Because Excel will highlight the whole chart area.
I need the most outer rows (upper + lowest), to always be
Red, the inner rows to always be Yellow, and the most
inner 5 rows to always be Green. I just can't figure it
out. That's why I highlighted 9 rows on the sheet, and
would like the cure line only to show - everything else
should be transparent, but i'm not sure that will work
either, because my rows are just simply rows, without any
scaling. I hope you got the picture by now.
Thanks.

-----Original Message-----
Oh, so you need to add the series that makes the
Click to expand...
line/curve. From the Chart menu,
 
If you want to show colored gridlines in the chart, you can use this technique, and
add one series for each colored line:

http://peltiertech.com/Excel/Charts/AddLineHorzSeries.html

If instead you'd like to actually color different bands in the chart background, use
this technique, but instead of a 2x2 pattern, make it 1 wide by N high:

http://peltiertech.com/Excel/Charts/ColoredQuadrantBackground.html

You could also adopt the conditional charting approach from this page, so the data
points themselves take on the appropriate color:

http://peltiertech.com/Excel/Charts/ConditionalChart1.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
The process is pretty much as on the example I cited.

Let's do the bands first. This is for five bands, each 10 units high:

bands
red 10
yellow 10
green 10
yellow 10
red 10

Select the data, make a stacked column chart, data in rows. Now a little formatting.
Double click a series, on the Options tab, set the gap width to zero. This makes the
columns as wide as the entire chart. Now color the columns, and set the border of
each to None, on the patterns tab.

Double click the vertical axis and select the Scale tab, set the max to 50, and
check the Axis Crosses at Maximum.

Here's some sample data:

Line
A 8
B 14
C 24
D 35
E 43

Copy the range, select the chart, choose Paste Special from the Edit menu. Choose
New Series, By Columns, Name in First Row, Categories in First Column. Well, that's
another column series that messed up the nice bands. So select the goofy bars,
choose Chart Type from the Chart menu, and choose a Line type. That fixed the bars.
Now double click the line, and on the Axis tab, click on Secondary, then format the
series on the Patterns tab. Select the chart, choose Chart Options on the Chart
menu, and on the Axes tab, check the Secondary X Axis box. Your choice whether to
keep or uncheck the secondary Y axis.

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

Thanks for reply, and for being patient with me. I did
the first three steps fine but when I came to this one:

"So select the goofy bars,
choose Chart Type from the Chart menu, and choose a Line
type. That fixed the bars.
Now double click the line, and on the Axis tab, click on
Secondary, then format"

I end up with 15 bars (from my data) plus my original 5
colums (red, yellow, green,etc) on the same plot are.
When I select the chart, and try to change the type to
Line, willn't let me do it. If select the entire area,
will just show a blank plot area! How can I do this step?

Thanks.
 
Are the 15 added bars all in the same series? They probably should be, so they'll be
represented by a series of points connected by a line.

You only need to click on one of the added bars, then change the series type. When
you say it wouldn't let you, how did it inform you of this?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Yes they are. It's from two rows (one with the actual
values, and the other with numbers from 1 to 15).
I did just that; clicked on one column then selected
chart type, selected Line type, but the bars still there,
no Line!! What's it that i'm doning wrong.


-----Original Message-----
Are the 15 added bars all in the same series? They
Click to expand...
probably should be, so they'll be
 
I really don't know. I suppose if you send me your workbook, I could take a look at it.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
I have tried to send you the file, but it came back as
your e-mail address doesn't exist.

-----Original Message-----
I really don't know. I suppose if you send me your
Click to expand...
workbook, I could take a look at it.
 
Take out the capital letters!

- Jon
I have tried to send you the file, but it came back as
your e-mail address doesn't exist.




workbook, I could take a look at it.
Click to expand...
 
Back
Top