# Scatter Plot point overlap

#### leydorfs

##### Board Regular
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.

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.

"...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?

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.

Thanks.

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.

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

Replies
1
Views
57
Replies
6
Views
143
Replies
2
Views
203
Replies
2
Views
101
Replies
14
Views
323

1,196,506
Messages
6,015,592
Members
441,903
Latest member
MG12345

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back