Scatter Plot point overlap

[leydorfs]

I have a scatter polt and many points have the same coordinates. The points overwrite each other, so that it looks like there is one point there. I would like to find a way to make each distinct point not overlap each other, but somehow have some kind of an offset, so if there are 5 points, at one coordinate, it really looks like 5 points.

Any idea how to do this?

Thanks.

 

[RalphA]

Say you have 3 points, x1=3, y1=5; x2=3, y2=5; x3=3, y3=5. Just make y1=4.9 and y3=5.1. That should do it.

 

[PaddyD]

"...I would like to find a way to make each distinct point not overlap each other, but somehow have some kind of an offset, so if there are 5 points, at one coordinate, it really looks like 5 points"

But if, on a 2 dimension graph, 5 items have the same value in each direction, then they all occupy the same point in the space?!?

In addition to Ralph's suggestion, why not post back with a little more info on the nature of the problem - the data you're plotting, why the overlapping points are an issue, some of the parameters of the set up - eg maximum likely number of points with same values, total number of points etc?

 

[leydorfs]

More Information

Thanks for the response. I want to use a scatter plot to show the regression line through the points. However, what I loose is that 30% of the points are in one spot. Example, suppose I have (4,5), (5,6), then 20 points at (6,7), it only looks like I have 3 equally recurring points, but I would like to show that there are a bunch of points at (6,7).

It sounds like ootsing it off of the actual point is the only way to do this -- and not that bad to automate using VB.

Any more advice?

Thanks.

 

[RalphA]

You could do a bubble chart, with the repeated points given a weight proportionate to tenumberof times itrepeates. Say, for the occurrance of five points at (7,8), give bubble at 6 a weightof 5, etc.

 

[wellinth]

I would draw a jitter plot of your data. In this plot, overlapping points are a moved a random amount (“jittered”) so that they are visible. Jitter plots are not a built in chart type in an Excel, but you can create them by writing custom VBA functions to “jitter” your data. Code for two such functions are shown below. Jitterx, jitters the x coordinate of one data point if it overlaps another and jittery jitters the y coordinate.

I would copy these functions into your Excel file, apply them to the identical points and draw an (xy) scatter chart of the jittered data. I’d be glad to send you a spreadsheet that does this.

Happy holidays,

- Tom Wellington

Email: thomaswellington@msn.com
Or tomwellington@sbcglobal.net

Function jitterx(x1, y1, x2, y2) As Double
' A function to return the jittered value of x2
' Provided the two points (x1,y1) and (x2,y2) are
' sufficently close.

' How close the two points have to be.
Const tol = 0.1
' Factor used to jitter x2.
Const factor = 0.25
' Local variables
Dim diffx As Double, diffy As Double
Dim StaticRand As Double
' Return a uniform random number between 0 and 1
' That is not changed when other cells change
Application.Volatile (False)
StaticRand = Rnd
' Calculate difference between xs and ys
diffx = x2 - x1
diffx = Abs(diffx)
diffy = y2 - y1
diffy = Abs(diffy)

If diffx <= tol And diffy <= tol Then ' Jitter x2
jitterx = x2 + factor * (StaticRand - factor)
Else
jitterx = x2
End If

End Function



Function jittery(x1, y1, x2, y2) As Double
' A function to return the jittered value of y2
' Provided the two points (x1,y1) and (x2,y2) are
' sufficently close.

' How close the two points have to be.
Const tol = 0.1
' Factor used to jitter y2.
Const factor = 0.25
' Local variables
Dim diffx As Double, diffy As Double
Dim StaticRand As Double
' Return a uniform random number between 0 and 1
' That is not changed when other cells change
Application.Volatile (False)
StaticRand = Rnd
' Calculate difference between xs and ys
diffx = x2 - x1
diffx = Abs(diffx)
diffy = y2 - y1
diffy = Abs(diffy)

If diffx <= tol And diffy <= tol Then ' Jitter y2
jittery = y2 + factor * (StaticRand - factor)
Else
jittery = y2
End If
End Function

 

[tusharm]

Take a look at the suggestions by and discussion 'tween Debra Dalgleish, Jon Peltier and myself in http://groups.google.com/group/micr...*+author:tushar&rnum=6&hl=en#88773b76fd054492

Obviously, I still haven't posted my tutorial. but that particular discussion may lead to a suitable solution.