A module of Tree functions for AFE

Trees.txt

Tree_Names = CHOOSECOLS(Tree_Data,1);

/**
Contains a table of trees in the format:

tree_name, tree_type, water_demand, mature_height
*/
Tree_Data = {
    "Elm English", "Broad-leafed", "High", 24;
    "Elm Wheatley", "Broad-leafed", "High", 22;
    "Elm Wych", "Broad-leafed", "High", 18;
    "Eucalyptus", "Broad-leafed", "High", 18;
    "Hawthorn", "Broad-leafed", "High", 10;
    "Oak English", "Broad-leafed", "High", 20;
    "Oak Holm", "Broad-leafed", "High", 16;
    "Oak Red", "Broad-leafed", "High", 24;
    "Oak Turkey", "Broad-leafed", "High", 24;
    "Poplar Hybrid Black", "Broad-leafed", "High", 28;
    "Poplar Lombardy", "Broad-leafed", "High", 25;
    "Poplar White", "Broad-leafed", "High", 15;
    "Willow Crack", "Broad-leafed", "High", 24;
    "Willow Weeping", "Broad-leafed", "High", 16;
    "Willow White", "Broad-leafed", "High", 24;
    "Acacia False", "Broad-leafed", "Moderate", 18;
    "Alder", "Broad-leafed", "Moderate", 18;
    "Apple", "Broad-leafed", "Moderate", 10;
    "Ash", "Broad-leafed", "Moderate", 23;
    "Bay Laurel", "Broad-leafed", "Moderate", 10;
    "Beech", "Broad-leafed", "Moderate", 20;
    "Blackthorn", "Broad-leafed", "Moderate", 8;
    "Cherry Japanese", "Broad-leafed", "Moderate", 9;
    "Cherry Laurel", "Broad-leafed", "Moderate", 8;
    "Cherry Orchard", "Broad-leafed", "Moderate", 12;
    "Cherry Wild", "Broad-leafed", "Moderate", 17;
    "Chestnut Horse", "Broad-leafed", "Moderate", 20;
    "Chestnut Sweet", "Broad-leafed", "Moderate", 24;
    "Lime", "Broad-leafed", "Moderate", 22;
    "Maple Japanese", "Broad-leafed", "Moderate", 8;
    "Maple_Norway", "Broad-leafed", "Moderate", 18;
    "Rowan", "Broad-leafed", "Moderate", 11;
    "Pear", "Broad-leafed", "Moderate", 12;
    "Plane", "Broad-leafed", "Moderate", 26;
    "Plum", "Broad-leafed", "Moderate", 10;
    "Sycamore", "Broad-leafed", "Moderate", 22;
    "Tree of Heaven", "Broad-leafed", "Moderate", 20;
    "Walnut", "Broad-leafed", "Moderate", 18;
    "Whitebeam", "Broad-leafed", "Moderate", 12;
    "Birch", "Broad-leafed", "Low", 14;
    "Elder", "Broad-leafed", "Low", 10;
    "Fig", "Broad-leafed", "Low", 8;
    "Hazel", "Broad-leafed", "Low", 8;
    "Holly", "Broad-leafed", "Low", 12;
    "Honey Locust", "Broad-leafed", "Low", 14;
    "Hornbeam", "Broad-leafed", "Low", 17;
    "Laburnum", "Broad-leafed", "Low", 12;
    "Magnolia", "Broad-leafed", "Low", 9;
    "Mulberry", "Broad-leafed", "Low", 9;
    "Tulip Tree", "Broad-leafed", "Low", 20;
    "Cypress Lawsons", "Coniferous", "High", 18;
    "Cypress Leyland", "Coniferous", "High", 20;
    "Cypress Monterey", "Coniferous", "High", 20;
    "Cedar", "Coniferous", "Moderate", 20;
    "Douglas Fir", "Coniferous", "Moderate", 20;
    "Larch", "Coniferous", "Moderate", 20;
    "Monkey Puzzle", "Coniferous", "Moderate", 18;
    "Pine", "Coniferous", "Moderate", 20;
    "Spruce", "Coniferous", "Moderate", 18;
    "Wellingtonia", "Coniferous", "Moderate", 30;
    "Yew", "Coniferous", "Moderate", 12
};

Get_Tree_Type = LAMBDA(tree_name, 
    INDEX(Tree_Data,XMATCH(tree_name,Tree_Names),2)
);

Get_Water_Demand = LAMBDA(tree_name, 
    INDEX(Tree_Data,XMATCH(tree_name,Tree_Names),3)
);

Get_Mature_Height = LAMBDA(tree_name, 
    INDEX(Tree_Data,XMATCH(tree_name,Tree_Names),4)
);

Get_Gradient = LAMBDA(x_1, y_1, x_2, y_2,
    (y_2 - y_1) / (x_2 - x_1)
);

/**
A table containing of the intercept points of lines plotted on the foundation_depths against depth_height_ratio charts.
{VCP, water_demand, tree_type, x_1, y_1, x_2, y_2}
*/
Intercept_Points = {
    // VCP, water_demand, tree_type, x_1, y_1, x_2, y_2
    "Low", "Low", "Broad-leafed", 0, 1.2, 0.5, 0.75;
    "Low", "Moderate", "Broad-leafed", 0, 1.6, 0.75, 0.75;
    "Low", "High", "Broad-leafed", 0, 2.5, 1.25, 0.75;
    "Low", "Moderate", "Coniferous", 0, 1.6, 0.35, 0.75;
    "Low", "High", "Coniferous", 0, 2.5, 0.6, 0.75;
    "Medium", "Low", "Broad-leafed", 0, 1.5, 0.5, 0.9;
    "Medium", "Moderate", "Broad-leafed", 0, 2, 0.75, 0.9;
    "Medium", "High", "Broad-leafed", 0.3, 2.5, 1.25, 0.9;
    "Medium", "Moderate", "Coniferous", 0, 2, 0.35, 0.9;
    "Medium", "High", "Coniferous", 0.2, 2.3, 0.6, 0.9;
    "High", "Low", "Broad-leafed", 0, 1.8, 0.5, 1;
    "High", "Moderate", "Broad-leafed", 0, 2.4, 0.75, 1;
    "High", "High", "Broad-leafed", 0.5, 2.5, 1.25, 1;
    "High", "Moderate", "Coniferous", 0, 2.4, 0.35, 1;
    "High", "High", "Coniferous", 0.35, 2, 0.6, 1
};

Get_Line_Gradient = LAMBDA(coordinates,
    LET(
        x_1, CHOOSECOLS(coordinates,1),
        y_1, CHOOSECOLS(coordinates,2),
        x_2, CHOOSECOLS(coordinates,3),
        y_2, CHOOSECOLS(coordinates,4),
        Get_Gradient(x_1, y_1, x_2, y_2)
    )
);

Get_Coordinates = LAMBDA(VCP, water_demand, tree_type,
    LET(
        table, Intercept_Points,
        VCPs, CHOOSECOLS(table,1),
        water_demands, CHOOSECOLS(table, 2),
        tree_types, CHOOSECOLS(table, 3),
        DROP(FILTER(table,(VCPs=VCP)*(water_demands=water_demand)*(tree_types=tree_type)),,3)
    )
);

Get_y_Intercept = LAMBDA(gradient,x,y,
    y - gradient*x
);

Get_Foundation_Depth = LAMBDA(tree_name, distance_from_building, volume_change_potential_of_soil,
    LET(
        VCP, volume_change_potential_of_soil,
        tree_type, Get_Tree_Type(tree_name),
        water_demand, Get_Water_Demand(tree_name),
        mature_height, Get_Mature_Height(tree_name),
        coordinates, Get_Coordinates(VCP,water_demand,tree_type),
        gradient, Get_Line_Gradient(coordinates),
        y_intercept, Get_y_Intercept(gradient,CHOOSECOLS(coordinates,1),CHOOSECOLS(coordinates,2)),
        distance_height_ratio, distance_from_building / mature_height,
        foundation_depth_temp, gradient * distance_height_ratio + y_intercept,
        foundation_depth_min, CHOOSECOLS(coordinates,4),
        foundation_depth, MAX(foundation_depth_min, foundation_depth_temp),
        IF(foundation_depth > 2.5, "Max foundation depth exceeded", foundation_depth)
    )
);

Hi. I've been reading through several of your gists and it's all super interesting! Would you be willing to share the documentation that guides the calculations in this function? I'm writing a series of articles on LinkedIn called "Lambda Spotlight" where I talk about the great work other LAMBDA users are doing. I'd like to write an article about this function if you're open to it. Please feel free to reach out to me on https://www.linkedin.com/in/owenhprice/

Hi. I've been reading through several of your gists and it's all super interesting! Would you be willing to share the documentation that guides the calculations in this function? I'm writing a series of articles on LinkedIn called "Lambda Spotlight" where I talk about the great work other LAMBDA users are doing. I'd like to write an article about this function if you're open to it. Please feel free to reach out to me on https://www.linkedin.com/in/owenhprice/

Hi Owen, thanks for checking my stuff out, it's just some stuff I've thrown together to experiment, I'm a structural Engineer in the UK. Most my lambda functions aren't generic but coded to solve some specific engineering problem or enhance a particular spreadsheet.

This particular gist of Lambda functions I've just updated as I never really liked it as the logic was pretty hard to follow (I copied and pasted some formula bar logic). I've updated it now to be a bit more comprehensible and lambda-esque. All the function does is turn these charts https://nhbc-standards.co.uk/4-foundations/4-2-building-near-trees/4-2-12-foundation-depth-charts/ into an intercept return function.

I've been reading your LinkedIn and it's good to see people being creative with lambdas which make excel a little more sane to use! I still find them a bit limiting, for example not being able to create an object and pass that as an argument which would make things a lot easier. I think your posts cover the best thing about it which is it supports First-Class functions including recursive loops which is the only form of native excel function to allow iteration (shame it doesn't support more loop types!).

A better lambda to show case may be one that has a recursive loop. My 'Steel_Connection.txt'. has a function that finds 'alpha' as defined in Appendix G of this document https://steelconstruction.info/images/5/5d/SCI_P398.pdf

https://gist.github.com/jsb2505/50e4de377b5f5c3876cfaaf2a94fdca4