Line Chart Data point colour change?

[DexonII]

Hi,

Does anybody know how I can get the series valuse on my line chart change colour if they are above or below the value in a specific cell?

I need to plot data to see if it falls inside two given limits. If it is above or below I would like it to change the data points of the series to red, but if it is within the limits remain as the default colour (black).

Any ideas? - Not VBA if possible!

Thanks in advance...

 

[Airfix9]

OK, here's my solution - there are bound to be others, though.

I created two dummy series for the graph, one showing what is inside the specified range (for this example, I chose 2-4) and one showing what is outside the range.

I then created a line graph using all three series.

For column C, I chose not to show the points
For column D (outside), I chose not to show the line and colour the points red
For column E (inside), I chose not to show the line and colour the points black

Ta-daaaaaaaaah!

Book5
	B	C	D	E
2			Rangeis2-4
3
4		Value	Outside	Inside
5	A	1	1	#N/A
6	B	3	#N/A	3
7	C	2	#N/A	2
8	D	2	#N/A	2
9	E	4	#N/A	4
10	F	5	5	#N/A
11	G	4	#N/A	4
12	H	5	5	#N/A
13	I	4	#N/A	4
14	J	6	6	#N/A
Graphing Solution

With thanks to Jon Peltier's most excellent site at http://peltiertech.com/Excel/Charts/index.html

 

[DexonII]

Thanks Airfix9,

I canot use more than 1 series for the chart. I am monitoring a process, and plotting the averages of 5 readings to create the plotted line.

I have seen somewhere - and I cannot now find where! - a line chart that appeared to have formatting of some kind on the actual series formula of the plotted line. It went alnog the lines of if the value if > or < specified values change the colour to red or black depending on which criteria was met.

ANy ideas???

 

[Airfix9]

But surely you are plotting from one set of data (that may expand). My example just adds two extra series of identical data with the irrelevant values reduced to "#N/A" (which will not plot)...

To be honest, that is about as far as my graphing experience goes. Have a good look at Jon Peltier's website - http://peltiertech.com/index.html -which has loads of tricks that you can do to solve your graphing problems.

Sorry I can't help further.

 

[DexonII]

Airfix9,

I cannot add in any additional columns as this will mess up the macrod running inside the sheet.

I will keep looking to see if I can find the example I saw. From memory the series had something along these lines in it to change the colour;

[Red][<5.8] #,##0.00;[Red][>7.8] #,##0.00;[Black] #,##0.00

Have tried all sorts, but cannot get it to work.

Thanks for your help.

ANy one else have any ideas??

 

[Airfix9]

Just one more thought, before I give up...

Why not transfer the graph data to another sheet (which can remain hidden) and add the two extra series there and graph from THAT sheet?

 

[DexonII]

*BUMP*

Has anybody any other ideas?