Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- Microsoft 365
- Excel
- Convex hull Lambda > would it be impossible to build?

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 04 2023 07:57 PM

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.

Labels:

15 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 08 2023 02:55 AM

Intriguing indeed! Have you tried chatGPT?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 08 2023 03:00 AM

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)

))

=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)

))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 08 2023 04:16 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 08 2023 04:18 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 08 2023 04:39 AM

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 08 2023 07:38 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 08 2023 07:41 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 08 2023 08:39 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 08 2023 09:23 PM

@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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 08 2023 09:31 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 01 2023 07:06 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 01 2023 07:28 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 01 2023 07:34 AM - edited Jun 01 2023 07:35 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 01 2023 08:46 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 01 2023 05:38 PM

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.