Forum Discussion

RecalcOrDie's avatar
RecalcOrDie
Iron Contributor
May 05, 2023

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 ...
Charting
excel
Formulas and Functions
Macros and VBA
ExcelWizard's avatar
ExcelWizard
Copper Contributor
Jun 01, 2023

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

https://1drv.ms/x/s!Ak8Fla2fCeo6gvFJ7YKoPlwaDwj23A?e=rU0dyn

RecalcOrDie's avatar
RecalcOrDie
Iron Contributor
Jun 01, 2023

ExcelWizard 

 

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. 

 

  • ExcelWizard's avatar
    ExcelWizard
    Copper Contributor
    Jun 01, 2023
    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.
    • RecalcOrDie's avatar
      RecalcOrDie
      Iron Contributor
      Jun 02, 2023

      ExcelWizard 

       

      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.

       

Resources