Hi,
I have three columns ( x, y and z). I want to create a graph of x versus y. I want all points with a non-zero value for z to appear red and all points with a zero value for z to appear blue. How would I accomplish this?
Thanks.
There exists no direct way to make the color of a chart point depend on the value. You can likely get the task done by a makro, or you generate two helper data series, one for “zero” and one for “non-zero” and display them instead of the original ones.
This can be accomplished by a simple re-organising of the data. The z column is essentially the category label. First sort the data based on the z column. For clarity, consider move the corresponding x and y value pairs, for all non-zero z labels, to a new set of columns.[1] This should result in:
Zero Non-zero
z0, x0, y0 z1, x1, y1
Use an XY (Scatter) chart type with x0,y0 as the first data series and x1,y1 as the second data series. The series will automatically use different symbols and colours.
[1] The downside of doing this is related to data point label display i.e., there are two (z) columns. If this is important perhaps use a range of row to represent each series.