Excel 2007 - chart conditional non-contigous range

**erupter** · 10-19-2011, 05:33 PM

I have a range of data.
I can chart/plot it just fine but I want to do more.
I want the resulting plot to use different colors based on the actual value of the data.

The idea I have is to create a number of different data sources, and give each of them a different color.
Then I could do the following
Series1: all values less then 2
Series2: values >2 and <4
and so on.

But how do I do this?
I found the offset function, but I don't know how to produce a range based on a condition.

Also if anyone has any proposal to solve this problem, I'm all ears.

An example of the possible result (color smoothing is not required in my application) is attached.

Edit: also I would like to use standard functions if possible, instead of looping through all the data points in VBA and color each of them separately.

**Andy Pope** · 10-20-2011, 03:50 AM

Plot multiple series, one for each colour. Use formula to either plot the point or NA() to not plot the point.

Post example workbook instead of picture if you need more help.

**erupter** · 10-20-2011, 06:05 AM

Originally Posted by Andy Pope

Plot multiple series, one for each colour. Use formula to either plot the point or NA() to not plot the point.

That's what I said: how do I tell the chart which points to plot and which not to plot?
Your hint to a formula and NA() doesn't help me much.
How do I define a series like that?
I think you're suggesting to make a copy of the data where the unwanted cells are marked #N/A
Is there a way to avoid making multiple copies of the data?

Post example workbook instead of picture if you need more help.

Take the one posted here.
It would be nice for example to display speeds below 5 in red and speed higher then 5 in green, or anything else. Just show me how I can do it.