Create a Convex Hull in Excel with LAMBDA in WPS Office
Question details
The user wants to create a convex hull around a set of points in Excel using a recursive LAMBDA formula instead of VBA.
- Product
- Microsoft Excel / WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has a set of XY coordinates in a worksheet and wants to identify and connect only the outer boundary points.
- Observed behavior
- The user is exploring a formula-based approach and has partial progress connecting the outer points, but needs a complete non-VBA convex hull method.
Make sure your spreadsheet application supports dynamic array functions such as LET, LAMBDA, REDUCE, MAP, FILTER, TAKE, DROP, CHOOSEROWS, and XLOOKUP. Prepare a two-column coordinate range with X and Y values and remove blank rows inside the data.
Build a Recursive LET and LAMBDA Formula to Return the Convex Hull
Use this method when you want a native formula solution that identifies the outer points and returns the ordered hull vertices without VBA.
The recursive LAMBDA starts from an initial hull point, then repeatedly selects the next point that has the smallest positive turn angle from the current edge. REDUCE and MAP handle the iterative point selection, while FILTER and XLOOKUP limit candidates to unused points.
Place X values in one column and Y values in the next column, for example B3:C99. Delete blank rows inside the range because the formula uses FILTER to exclude empty cells.
Select a blank cell, paste the recursive LET/LAMBDA formula, and update the xy range B3:C99 to match your data. The pattern starts with =LET(xy,B3:C99,z,FILTER(xy,TAKE(xy,,1)<>""),...), then uses REDUCE, MAP, XLOOKUP, ATAN2, and MOD to pick the next hull vertex.
Press Enter. In a dynamic array-enabled version of WPS Spreadsheet or Excel, the formula spills the ordered hull point coordinates. If you see a #SPILL! error, clear enough blank cells for the output.
Select the original points and the spilled hull coordinates, go to Insert > Scatter, and add the hull series to visualize the outer boundary around the point set.
Use a VBA Convex Hull User-Defined Function as an Alternative
Use this method if your spreadsheet version does not support dynamic array functions or you prefer an encapsulated macro function.
Create an Excel-Compatible Convex Hull in WPS Spreadsheet
WPS Spreadsheet supports Excel-compatible dynamic array functions, so you can apply the same recursive LET/LAMBDA convex hull formula, return the ordered hull vertices, and build a scatter chart without paying for Microsoft 365.
- 1. Open your coordinate workbook: Launch WPS Spreadsheet and open the workbook that contains the XY point data.
- 2. Enter the LAMBDA formula: Click a blank cell, paste the recursive LET/LAMBDA convex hull formula, and adjust the coordinate range to match your data.
- 3. Check the spilled hull points: Press Enter and confirm that the ordered hull vertex coordinates spill into the worksheet. Clear adjacent cells if a #SPILL! error appears.
- 4. Visualize the convex hull: Select the original data and the hull output, go to Insert > Scatter, and add the hull series to the chart.
Frequently Asked Questions
Can Excel's LAMBDA really create a convex hull without VBA?
Yes. A recursive LET/LAMBDA formula can start at an extreme point and repeatedly choose the next outer point using REDUCE, MAP, XLOOKUP, FILTER, ATAN2, and MOD until it returns to the start.
Why does my LAMBDA convex hull formula return #SPILL! or #CALC!?
Check that your version supports dynamic array functions, that the coordinate range has no blank rows inside the data, and that enough empty cells are available for the spilled output.
Which versions of WPS Spreadsheet support LET, LAMBDA, REDUCE, and MAP?
Recent WPS Office versions with dynamic array support include these functions. If they are not available in your version, use an alternative VBA convex hull UDF or update WPS Office.
How do I chart the convex hull after the formula returns the points?
Select the returned hull coordinates, go to Insert > Scatter, and add them as a series to the chart that already displays the original XY points.
Is there a simpler alternative if the recursive formula is too complex?
A VBA convex hull UDF can accept a range and return the hull vertices, but it requires macros to be enabled and the workbook saved in a macro-enabled format such as .xlsm.
