Draw a convex hull in Excel?

[Bob]

I'm using Excel 97, and would like to draw a convex hull (an enclosing line)
around several groups of points in a scattergram. The idea is to show how
the groups overlap each other. I could just link all the outer points of
each group with lines, but is there a neater way?

 

[Tushar Mehta]

Don't often get to read the term convex hull in an XL newsgroup

Close the loop, so to say, by duplicating the first point as the last
point in a XY Scatter chart. So, to form a rectangle with (1,1),
(2,1), (2,2), and (1,2), add the first point once again. Now, create a
XY Scatter chart with these 5 points. Make sure you use a subtype that
contains a line.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

I thought he was talking about designing a submarine!

There have been macros posted which draw a polygon connecting the points
of an XY series, so you could actually fill in the enclosed shape with
your favorite color. Here's a simple one I put together:

Sub DrawAShape()
Dim myCht As Chart
Dim mySrs As Series
Dim Npts As Integer, Ipts As Integer
Dim myShape As Shape
Dim Xnode As Double, Ynode As Double
Dim Xmin As Double, Xmax As Double
Dim Ymin As Double, Ymax As Double
Dim Xleft As Double, Ytop As Double
Dim Xwidth As Double, Yheight As Double

Set myCht = ActiveChart
Xleft = myCht.PlotArea.InsideLeft
Xwidth = myCht.PlotArea.InsideWidth
Ytop = myCht.PlotArea.InsideTop
Yheight = myCht.PlotArea.InsideHeight
Xmin = myCht.Axes(1).MinimumScale
Xmax = myCht.Axes(1).MaximumScale
Ymin = myCht.Axes(2).MinimumScale
Ymax = myCht.Axes(2).MaximumScale

Set mySrs = myCht.SeriesCollection(1)
Npts = mySrs.Points.Count

Xnode = Xleft + (mySrs.XValues(Npts) - Xmin) _
* Xwidth / (Xmax - Xmin)
Ynode = Ytop + (Ymax - mySrs.Values(Npts)) _
* Yheight / (Ymax - Ymin)

With myCht.Shapes.BuildFreeform(msoEditingAuto, Xnode, Ynode)
For Ipts = 1 To Npts
Xnode = Xleft + (mySrs.XValues(Ipts) - Xmin) _
* Xwidth / (Xmax - Xmin)
Ynode = Ytop + (Ymax - mySrs.Values(Ipts)) _
* Yheight / (Ymax - Ymin)
.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode
Next
Set myShape = .ConvertToShape
End With

With myShape
.Fill.ForeColor.SchemeColor = 13 ' YELLOW
.Line.ForeColor.SchemeColor = 12 ' BLUE
End With
End Sub

- Jon

 

[Bob]

The tips were much appreciated- I'll experiment with both methods

 

[Tushar Mehta]

Interestingly, the basic design of a submarine hull is very close to a
convex hull. The practical bit I remember about a convex hull is that
it is a n-dimensional object such that the straight line connecting any
2 points on the surface or anywhere in the interior will not intersect
with the surface. So, inspired by an ad I'm watching promoting
bananas... an orange has a convex hull, but not a banana.

--
Regards,

Tushar Mehta
MS MVP Excel 2000-2003
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office

 

[Jon Peltier]

A dozen or so years ago, my employer was responding to a government RFQ
to investigate alternative materials and construction methods for
submarines. To learn a little about submarines, I reread Tom Clancy's
"The Hunt for Red October", which is crammed with technical details on
the topic. That three hours was more productive than three days would
have been in the library. Of course, that was before the internet, so
I'm sure now I could get all that information in ten minutes, he he.

But we Bostonians are still Hunting for a Red Sox October.

- Jon