Iron Contributor

Convex hull Lambda > would it be impossible to build?

Hello everyone,

Ever since visiting the site of Andy Pope, I saw a chart that fascinated me because it can be very useful for creating the bounding area of a set of points.  It's known as Convex Hull.  Here's Andy's post > https://andypope.info/charts/convexhull.htm

In the post you can download the file, and the VBA is fascinating to say the least.

I was wondering, with the LAMBDA technology and its possibility for recursiveness, is it possible with Lambda or has anyone come across a Lambda formula about it?

Thanks in advance.

15 Replies

Re: Convex hull Lambda > would it be impossible to build?

Intriguing indeed! Have you tried chatGPT?

Re: Convex hull Lambda > would it be impossible to build?

I did. After some discussion with it, it came up with this:
=LAMBDA(points,
LET(
leftmost_point, MIN(points, 1),
x_values, INDEX(points, 0, 1),
y_values, INDEX(points, 0, 2),
n, ROWS(points),
indices, SEQUENCE(n),
indices_sorted, SORTBY(indices, x_values),
upper_hull_indices, FILTER(indices_sorted, y_values >= LINEST(y_values, x_values,,TRUE)),
lower_hull_indices, FILTER(indices_sorted, y_values < LINEST(y_values, x_values,,TRUE)),
upper_hull_points, INDEX(points, upper_hull_indices),
lower_hull_points, INDEX(points, lower_hull_indices),
upper_hull_sorted, SORTBY(upper_hull_points, y_values, -1),
lower_hull_sorted, SORTBY(lower_hull_points, y_values),
upper_hull_final, IF(COUNT(upper_hull_sorted) > 1,
TRANSPOSE({INDEX(upper_hull_sorted, 1),INDEX(upper_hull_sorted,2)}) ,
upper_hull_sorted),
lower_hull_final, IF(COUNT(lower_hull_sorted) > 1,
TRANSPOSE({INDEX(lower_hull_sorted, 1),INDEX(lower_hull_sorted,2)}) ,
lower_hull_sorted),
hull_points, SORTBY(TRANSPOSE({leftmost_point, upper_hull_final, lower_hull_final}), x_values)
,
TEXTJOIN(",",TRUE,hull_points)
))

Re: Convex hull Lambda > would it be impossible to build?

Convincing perhaps, but Excel doesn't like the {INDEX(upper_hull_sorted, 1),INDEX(upper_hull_sorted,2)} bits in there.

Re: Convex hull Lambda > would it be impossible to build?

No, expressions aren't allowed in literal arrays {...}, only constant values.

Re: Convex hull Lambda > would it be impossible to build?

Yes. And because I couldn't enter the formula I gave up having real work to do rather than fool around chatting with am artificial intelligence. Lets put Actual Intelligence to work!

Re: Convex hull Lambda > would it be impossible to build?

Afraid to generate correct answer with AI tool you need more knowledge formulating proper questions rather than to generate the formula by your own from scratch.

Re: Convex hull Lambda > would it be impossible to build?

I totally agree @SergeiBaklan I tried to ask the bot to not use curly braces at all, but it insists on using them (and in the wrong way as well).
This was its final attempt:
=LAMBDA(points,
LET(
leftmost_point, MIN(points, 1),
x_values, INDEX(points, SEQUENCE(ROWS(points)), 1),
y_values, INDEX(points, SEQUENCE(ROWS(points)), 2),
n, ROWS(points),
indices, SEQUENCE(n),
indices_sorted, SORTBY(indices, x_values),
upper_hull_indices, FILTER(indices_sorted, y_values >= LINEST(y_values, x_values,,TRUE)),
lower_hull_indices, FILTER(indices_sorted, y_values < LINEST(y_values, x_values,,TRUE)),
upper_hull_points, INDEX(points, upper_hull_indices, ),
lower_hull_points, INDEX(points, lower_hull_indices, ),
upper_hull_sorted, SORTBY(upper_hull_points, INDEX(upper_hull_points, SEQUENCE(ROWS(upper_hull_points)), 2), -1),
lower_hull_sorted, SORTBY(lower_hull_points, INDEX(lower_hull_points, SEQUENCE(ROWS(lower_hull_points)), 2)),
hull_points, SORTBY(
INDEX(
TRANSPOSE(
{
INDEX(upper_hull_sorted, 1),
IF(ROWS(upper_hull_sorted)>1, INDEX(upper_hull_sorted, 2), INDEX(lower_hull_sorted, 1)),
IF(ROWS(lower_hull_sorted)>1, INDEX(lower_hull_sorted, ROWS(lower_hull_sorted)-1), INDEX(lower_hull_sorted, 1))
}
)
),
INDEX(
TRANSPOSE(
{
INDEX(upper_hull_sorted, 1, 1),
IF(ROWS(upper_hull_sorted)>1, INDEX(upper_hull_sorted, 2, 1), INDEX(lower_hull_sorted, 1, 1)),
IF(ROWS(lower_hull_sorted)>1, INDEX(lower_hull_sorted, ROWS(lower_hull_sorted)-1, 1), INDEX(lower_hull_sorted, 1, 1))
}
)
)
)
)
)

and it stated:
This version does not use curly braces and should work as a LAMBDA formula in Excel.
which is nonsense.

Re: Convex hull Lambda > would it be impossible to build?

Can't comment details, I don't use AI. Tried with the Bing for a while, but traditional search (which after all is also AI) gives more relevant results. Perhaps in few years the tool will be more reliable, who knows. At the moment that's just a toy. But Excel MVPs as you are have to go with it, hope you give adequate feedback to Microsoft.

Re: Convex hull Lambda > would it be impossible to build?

@JKPieterse Thanks of your comments, I have tried like dozen of Lambdas out from ChatGtP but none worked.  I still need to work on grasping recursiveness. Sometimes I feel like "I get it" and then in another case I lost it. :unamused_face: But I'm making progress. Learning -- thunking -- has been fun.

Re: Convex hull Lambda > would it be impossible to build?

To all,

Here's another person: Fernando Cinquegrani, who developed a Convex hull UDF. Very interesting stuff. It would be nice to check out his VBA back-end but its locked.

Fernando's site: http://www.prodomosua.it/ppage02.html

On the link above, you will be able to find the workbook with the convex hull right above the section where the graph below appears.

Re: Convex hull Lambda > would it be impossible to build?

I got some progress to connect the outer point.

=LET(xy,B3:C99,z,FILTER(xy,TAKE(xy,,1)<>""),r,SEQUENCE(ROWS(z)),st,XMATCH(0,DROP(z,,1),1),
RAD,LAMBDA(p,q,ATAN2(INDEX(q,1)-INDEX(p,1),INDEX(q,2)-INDEX(p,2))),
pl,REDUCE(HSTACK(st,0),r,LAMBDA(a,i,IF((ROWS(a)>1)*(INDEX(a,ROWS(a),1)=st),a,
LET(b,CHOOSEROWS(z,TAKE(a,-1,1)),
Nx,FILTER(r,ISNA(XMATCH(r,DROP(VSTACK(TAKE(a,,1),0),1)))),
Nr,XLOOKUP(0,MAP(Nx,LAMBDA(n,MOD(RAD(b,CHOOSEROWS(z,n))-TAKE(a,-1,-1),2*PI()))),Nx,,1),
VSTACK(a,HSTACK(Nr,RAD(b,CHOOSEROWS(z,Nr)))))))),

CHOOSEROWS(z,TAKE(pl,,1)))

ConvexHull.xlsb

Re: Convex hull Lambda > would it be impossible to build?

@ExcelWizard

Forget about ChatGTP
It´s BoGTP ... woah! amazing, simple amazing Bo. Hats off.

Re: Convex hull Lambda > would it be impossible to build?

I'm a hardcore ALT + ENTER + Space Bar kind of guy, so formatting formulas to be vertically is imperative for me.  And in reviewing the formula, I have to ask, what´s this %% (double percentages)? And also, what's this secret function =RAD() ?

note: I'm not a LAMBDA expert like the brilliant minds that have posted here. Yet, I'm always eager to learn the -- core concepts -- of interesting and complex formulas.

Re: Convex hull Lambda > would it be impossible to build?

z is column 1 is X , column 2 is Y
XMATCH(0,DROP(z,,1)-TAKE(z,,1)%%,1) => XMATCH(0,Y-X%%,1)
% is divided by 100
Y - X /100 /100 This is to get the lowest point on Y and max point on X
if we have
x10,y5 x10, y1 and x100 y1 this will give you the x100 y1 the bottom right point
but I realize that is not necessary. any bottom y point will work.

RAD,LAMBDA(p,q,ATAN2(INDEX(q,1)-INDEX(p,1),INDEX(q,2)-INDEX(p,2)))
This is the Lambda function to calculate the angle in radians of 2 points from P and Q.

Re: Convex hull Lambda > would it be impossible to build?

Bo, right now I'm like a little kid with a new toy. Thank you for this! You leave me with good homework to study this mind-blowing formula. I'm attaching this updated version with pivot table slicers for switching the set of dots.

Lastly, I have messaged Andy Pope about this post on this forum.