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