Adding median lines to xy scatter graph

**Richard Buttrey** · 10-18-2008, 03:04 PM

Hi,

This is probably one for Andy, but ideas welcome from all.

I have two sets of data (i.e. several rows and two columns), both with values in the range 0 - 1. I'm plotting these on an xy scatter chart

The two median values for these two sets of data now need to be plotted on the chart as two lines, one horizontal and one vertical, effectively dividing the chart into 4 quadrants.

At the moment I've been achieving this functionality somewhat artificially, by superimposing 4 rectangle objects on the chart and adjusting their dimensions manually to coincide with the two median values on the two axes.

In addition I've been manually adding two text boxes each containing one of the median values and positioning it next to the appropriate median 'line'.

My question is this.

Is there a neater way of achieving this functionality without the somewhat artificial method of resorting to overlaid rectangles?

I can adjust the 4 rectangles programatically by changing the height and width property, but the text box needs to be positioned and there doesn't appear to be a Top and Left property.

All ideas gratefully received. Usual TIA

**shg** · 10-18-2008, 03:39 PM

Why not add two series, each with two points?

xmed, ymin
xmed, ymax

xmin, ymed
xmax, ymed

**Richard Buttrey** · 10-18-2008, 04:52 PM

Hi shg,

Good lateral thinking I'll give that a whirl.

Ah, just remembered that it's the individual x,y plots that are the relevant factor here - hence the x,y scattergraph. A straight forward series plot wouldn't achieve that.

One of the other requirements was to have two different fill colours for the top right hand and bottom left quadrants - hence the artificial use of overlaid rectangles.

Any thoughts on that aspect?

Regards

**shg** · 10-18-2008, 06:12 PM

A straight forward series plot wouldn't achieve that.

Wouldn't achieve what?

One of the other requirements was to have two different fill colours for the top right hand and bottom left quadrants - hence the artificial use of overlaid rectangles.

Create a picture having the desired quadrant colors using your favorite draw program, and then use it to fill the plot area:

Format > Selected Plot Area, Fill Effects, Select Picture ...

**Richard Buttrey** · 10-19-2008, 02:31 AM

Sorry, didn't explain that well enough. The two series lines would just plot a line, or points for each series. What's needed is the co-ordinate plot of each pair of values.

As for the rectangles, if you think of the whole chart area, it will be dotted with plotted points. Each of the two series has a median value. Those two median values need to be used to somehow divide the chart into 4 quadrants, by adding the median lines perpendicular to both the x & y axes. The upper right and lower left quadrants thus formed need to have a coloured background.

I've been using overlaid rectangles to achieve this, but manually dragging the borders to the appropriate points on the chart. I know I can do this programmatically, I just wondered if there was any chart functionality that would achieve the same thing. The other requirement is to position two text boxes, each of which contains the text "Median = 23" (or whatever the median value is), close to the median line.

Thanks for your input.

Regards

**Andy Pope** · 10-19-2008, 07:03 AM

Can you post example of your data set and if possible one that has the autoshapes to provide colour and labeling.

It's certainly possible to use additional series to draw the quadrents and even hold Median information. The biggest problem is the colouring of sectors.
This is not an automatic feature in Windows, although I think the Mac version can do filled xy-scatters.

Would a coded solution be usable?

**Richard Buttrey** · 10-19-2008, 08:17 AM

Thanks Andy,

I'm on my Mac at the moment, but the solution is required for Windows. I'll send the details a bit later this afternoon, along with the autoshapes when I have access to the PC.

Thanks for responding.

Regards,

Richard

**Richard Buttrey** · 10-19-2008, 01:04 PM

Originally Posted by Andy Pope

Can you post example of your data set and if possible one that has the autoshapes to provide colour and labeling.

It's certainly possible to use additional series to draw the quadrents and even hold Median information. The biggest problem is the colouring of sectors.
This is not an automatic feature in Windows, although I think the Mac version can do filled xy-scatters.

Would a coded solution be usable?

Hi Andy,

Yes a coded solution would be perfectly acceptable, as would any other method of easily indicating the points which are in the top right and bottom left quadrants. I've been thinking about colouring the labels attached to the points or puting them in larger font, although the idea of a coloured rectangle seems more intuitive. The point is that the medians change with different data sets and hence they need change dynamically on the chart. At the moment it's the inside borders of the rectangles which are effectively the median lines, in this case .38 on the x axis and .33 on the y axis.

I'm attaching an extract from the application. I was hoping that I could generate an xy pivot chart directly from a pivot table or even the original data, but apparently not. Hence in the example I'm attaching you can see that I've a range outside the pivot table which is linked to cells in the table.

Regards,

Richard

**Andy Pope** · 10-20-2008, 04:39 AM

The attached is a non coded solution.

Points is xy-scatter series
Outline of quadrents is formed from 3 xy-scatter series
3 Fills are stacked area series

The fills are area charts, plotted as dates. Additional padding series is required for bottom right quadrent.

The information is all driven from the values in N1:O3

If you need further help understanding any of the steps just post back.

**Richard Buttrey** · 10-20-2008, 08:54 AM

Many thanks for the prompt reply Andy.

I'll take a detailed look later today.

Regards

Richard

**Richard Buttrey** · 10-20-2008, 06:13 PM

Hi Andy,

This degree of lateral thinking is quite remarkable - as per usual! You've taken charts to a level I suspect not even the MS designers envisaged.

Just a couple of supplementary Qs.

You mention 3 fills. By this do you mean the 2 fills for the separate upper right and lower left quadrants, plus a third fill which covers both the top left and bottom right?

In the comment to W1 you mention that the x values are multiplied by 100 because they are treated as dates. Can you explain the significance of that and why the reference to 'dates'.

Many thanks once again

**Andy Pope** · 10-21-2008, 04:36 AM

The 3 fills are for
Lower Left
Padding Lower Right
Upper Right

Because the area chart is a stacked area the upper right quadrent is stacked on lower right quadrent. You can see this more clearly if you select the chart and then on the Layout tab use the dropdown list in Current selection group to select the series "PadTopRight". Once selected apply a fill colour to see bottom right quadrent with colour.
If you wanted the also colour top left you could either use the plotarea or add a 4th series stacked on top of "LowerLeftQad" series.

In order to get finer control over where the area charts appear you have to use Time series rather than category axis. And when using Time series it's simpler to use large numbers as the scale wants to deal in days.

**issi09** · 12-08-2010, 09:05 PM

I would really appreciate if you can help me with a scatter plot I have been trying to create since past 3 hours. I have to plot return on equity Vs. price to tangible book. In addition I have to create quadrants based on median value of each column. Seemingly simple, but I have not been able to make any headway. All attempts to replicate the solution given by Andy has failed miserably. Please help - this is sorta urgent. Excel sheet is attached for reference. Many Thanks! [QUOTE=Andy Pope;1983804]The 3 fills are for
Lower Left
Padding Lower Right
QUOTE]