Thread: Conditional formatting data points within a chart?

I need points that plot within a chart to change color based on a set value. For example: 0, then the circle is filled in with yellow color; -2, then the circle is colored pale red; -4 or greater, then bright red; +2, then pale green; +4 or greater, then bright green. Thanks much.

You need to use multiple series to plot the various colours.
And you use formula to test the original data and either use that value or NA() to surpress the point.

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

Yes you would need to test whether the value falls within a value range for the colour being plotted. As explained on the page I provided a link to.

You have 5 colour bands so will need 5 additional series.

Not sure how the 10 series fit's in. If all of your 10 series follow the same colour coding rules how will you be able to tell 1 data point from another?

The principle is correct but your ranges are confusing.

you can not be greater than zero AND equal to it.
4 to -4 overlaps with the first 3 groups.
And your test of negatives is wrong. As -4 is less than -2 not greater than.
So the formula in F5 should be

=IF($D5<F$2,IF($D5>=F$3,$D5,NA()),NA())

Sorry, my English is not very good.
Perhaps what you did is some values donot appear and you want all of them must take each value a point on chart?
If so, please see my chart.

You need to change your formula.

=IF($D5>=E$2,IF($D5<=E$3,$C5,NA()),NA())

This will test for the values being between or equally to the min/max values.
If the difference value falls within the range you need to output the Y value rather than the difference value.

Now I can understand, so change the formula
=IF(AND($D5>=E$2;$D5<=E$3);$D5;NA())

to be
=IF(AND($D5>=E$2;$D5<=E$3);$C5;NA())

then it would correct.