It’s been a busy week, and I haven’t had time to write any new posts. However, in a comment to VBA Conditional Formatting of Charts by Value, Dean asked how to conditionally format points in an XY chart according to which quadrant they fall in, where the quadrants are determined by the mean X and Y values. Raqther than writing a long comment in response, I decided to write a short post.

Of course, you can format any chart’s data points with VBA, according to any conditions you want. But some things are easier to do and less complicated to maintain if you use Excel’s built in worksheet functions. I have covered worksheet-based conditional formatting of charts in Simple Conditional Charts and Simple Conditional Charts – 2. But it’s good to write up more examples of suitable formulas to match the formatting criteria, to help people see when they can use this technique.

To illustrate my answer to Dean’s question, I generated the following data. Columns A and B have the original X and Y data, which I sampled from normal distributions. The mean values for X and Y are computed in A1 and B1. Columns C through F contain the Y values for each of the quadrants, determined by a simple set of formulas.

The formulas in C3 through F3 are as follows, and are filled down to row 14:

C3: =IF(AND($A3>=$A$1,$B3>=$B$1),$B3,NA())
D3: =IF(AND($A3<$A$1,$B3>=$B$1),$B3,NA())
E3: =IF(AND($A3<$A$1,$B3<$B$1),$B3,NA())
F3: =IF(AND($A3>=$A$1,$B3<$B$1),$B3,NA())

Here is a chart of the original data, using A2:B14 as the chart’s source data range:

Here is the chart showing the data by quadrant, with one series per quadrant, using column A for all X values and columns C through F for the Y values of the four series:

Related Posts:

• Split Data Range into Multiple Chart Series without VBA
• VBA Conditional Formatting of Charts by Category Label
• VBA Conditional Formatting of Charts by Value
• VBA Conditional Formatting of Charts by Series Name
• Category Axis Tricks for Line and Area Charts – 1
• Pivot Table Conditional Formatting with VBA
• You Say “Pie”, I Say “Bar”
• Area Chart – Invert if Negative
• Stack Columns In Order Of Size
• Physics Lesson