| # 5G LAMBDA Template Skill | | | # Version: 1.4 | | | # Last Updated: May-20-2026 | | | skill: | |
| name: lambda-template-5g | |
| description: > | |
| Rules and conventions for creating well-structured Excel LAMBDA functions | | | with inline help documentation using the Advanced Formula Environment (AFE). | | | Follows 5th Generation Modeling standards from 5gmodelling.com. | | | category: Excel | | | tags: | | | - LAMBDA | | | - AFE | | | - Excel Labs | | | - Named Functions | | | - Documentation | | | - 5G Modeling | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # TRIGGER PHRASES | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | triggers: | | | - "Make 5g" | | | - "Make LAMBDA" | | | - "Convert to 5g" | | | - "Convert to LAMBDA" | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # WORKFLOW | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | workflow: | | | description: > | | | Step-by-step process to convert a formula into a 5G LAMBDA function. | | | Do not scan the workbook before taking step 1. | | | steps: | | | - step: 1 | | | action: Ask for user's name | | | prompt: "What name shall I use for version control?" | | | usage: Used in REVISIONS section and VERSION line | | | - step: 2 | | | action: Ask for formula | | | prompt: "Select formulas to convert into a 5G LAMBDA function." | | | usage: Identifies parameters and core logic | | | - step: 3 | | | action: Display parameters and ask if any are optional | | | prompt: "Are any of these inputs optional? If yes, which ones and what is each optional parameter's default value?" | | | usage: Determines bracket conventions and default value logic | | | - step: 4 | | | action: Display suggested function names and ask for function name | | | prompt: "What would you like to name this function? (e.g., MonthlyPaymentΞ»)" | | | usage: Used throughout the LAMBDA definition and help text | | | - step: 5 | | | action: Add to names collection | | | description: Add the 5G LAMBDA function to the workbook's names collection | | | - step: 6 | | | action: Test the function | | | description: Find an empty space below the original example and run the 5g function with the same inputs as the original example. | | | Leave the test results for the user to examine. Do not clean them up. | | | - step: 7 | | | action: Compare results | | | description: Compare the 5G function's output to the original example to verify correctness | | | - step: 8 | | | action: Display Source | | | description: Write the fully commented source to the chat window. Use TEMPLATE STRUCTURE as a guide. | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # RULES | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | rules: | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # 1. PLACEHOLDER BRACKETS | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | placeholder_brackets: | | | description: > | | | Template placeholders like <description> are instructional only. | | | Remove the angle brackets (<>) in the generated output. | | | example: | | | template: "<Calculates the monthly payment>" | | | output: "Calculates the monthly payment" | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # 2. USER NAME | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | user_name: | | | description: > | | | Always ask for the user's name at the start of the workflow. | | | Use it in the REVISIONS section and VERSION line. | | | prompt: "What is your name?" | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # 3. DATE HANDLING | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | date_handling: | | | description: > | | | Use the current date for the REVISIONS section and VERSION line. | |
| format: "Mmm-dd-yyyy" | |
| example: "May-15-2026" | |
| # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # 4. OPTIONAL PARAMETERS | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | optional_parameters: | | | description: > | | | Ask if any inputs are optional. If yes, ask which ones and | | | what each optional parameter's default value is. | | | prompt: "Are any of these inputs optional? If yes, which ones and what is each optional parameter's default value?" | | | handling: | | | lambda_definition: "All arguments use [brackets]" | | | help_text_function_line: | |
| required: "NO brackets (e.g., Amount)" | |
| optional: "WITH brackets (e.g., [Term])" | |
| help_text_parameters: | |
| required: "(Required) description" | |
| optional: "(Optional) Default: value. description" | |
| let_section: > | |
| Add default value assignment for optional params: | | | ParamName, IF(ISOMITTED(ParamName), DefaultValue, ParamName), | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # 5. ARGUMENT BRACKETS CONVENTION | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | argument_brackets: | | | description: > | | | All arguments must appear optional to Excel so help text can display | | | when required arguments are omitted. The help text signals which are | | | truly required by omitting brackets. | | | lambda_definition: | |
| required: "[Argument]" | |
| optional: "[Argument]" | |
| help_text_function_line: | | | required: "ARGUMENT" | | | optional: "[Argument]" | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # 6. TEXTSPLIT DELIMITERS | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | textsplit_delimiters: | | | description: > | | | TEXTSPLIT creates a formatted help table that displays | | | in the Excel grid when required arguments are omitted. | | | syntax: 'TEXTSPLIT( text, "β", "ΒΆ" )' | | | delimiters: | | | column: "β" | | | row: "ΒΆ" | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # 7. MULTI-ROW PARAMETER DESCRIPTIONS | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | multi_row_descriptions: | | | description: > | | | When a parameter description needs multiple rows in the grid display, | | | use a leading "β" on continuation lines for proper column alignment. | | | example: | | | | " PositiveOnly? β(Optional) TRUE: Outflows are entered as positive values ΒΆ" & | | | "β that this function will subtract from the opening balance. ΒΆ" & | | | "β FALSE: Outflows are entered as negative values. Default is FALSE." | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # 8. LET STEP RESULTS | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | let_step_results: | | | description: > | | | Name the last calculation in the LET Function. | | | Use that name as the last step in the LET function. | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # 9. EXCEL SYNTAX RULES | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | excel_syntax: | | | description: > | | | Follow standard Excel conventions for function names and booleans. | | | booleans: "Uppercase: FALSE, TRUE" | | | function_names: "Uppercase: AVERAGE(), INDEX(), SCAN(), REDUCE(), PMT()" | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # 10. STRING CONCATENATION | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | string_concatenation: | | | description: > | | | Ensure all help text lines are properly concatenated with the & operator. | | | rule: "Every line except the last must end with &" | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # 11. INDEX BEHAVIOR | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | index_behavior: | | | description: > | | | When working with single-row arrays, INDEX interprets the second | | | argument as the column number. | | | rule: "When array is a single row, INDEX(array, n) returns the nth column value" | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # 12. MULTIPLE FORMULAS | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | multiple_formulas: | | | description: > | | | When the user selects multiple cells containing formulas, | | | include all of them in the LAMBDA function. | | | handling: | | | - Identify all formulas in the selected range | | | - Combine them logically within a single LAMBDA | | | - Create appropriate parameters for all unique inputs across formulas | | | - Document each formula's purpose in the help text | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # 13. INPUT CELL DETECTION | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | input_cell_detection: | | | description: > | | | When formulas reference cells outside the selected range, determine | | | which cells become LAMBDA inputs based on their content. | | | rules: | | | - If a referenced cell contains a value (not a formula), it becomes an input | | | - If a referenced cell contains a formula that only references another cell | | | (passthrough), follow the reference chain to find the true input | | | - If a referenced cell contains a formula with calculations, that cell | | | itself becomes an input | | | example: | | | scenario: "Selected formula references B5, and B5 contains =C3" | | | result: "C3 becomes the input, not B5 (B5 is a passthrough)" | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # 14. HORIZONTAL SEQUENCE FOR SCAN | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | horizontal_sequence_scan: | | | description: > | | | When using SCAN to build a horizontal array, use SEQUENCE(1, n) | | | instead of SEQUENCE(n) to avoid needing TRANSPOSE afterward. | | | rule: "SEQUENCE(1, Term) produces horizontal output directly from SCAN" | | | example: | | | inefficient: | | |
| CloseBal, SCAN(Amount, SEQUENCE(Term), LAMBDA(bal, n, formula)), | |
| CloseRow, TRANSPOSE(CloseBal), | |
| elegant: | | | | CloseBal, SCAN(Amount, SEQUENCE(1, Term), LAMBDA(bal, n, formula)), | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # 15. DROP VS TAKE FOR ARRAY SLICING | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | drop_vs_take: | | | description: > | | | When removing elements from the end of an array, prefer DROP over TAKE | | | to avoid referencing array length variables. | | | rule: "Use DROP(array, , -1) instead of TAKE(array, 1, Length-1)" | | | example: | |
| inefficient: "OpenBal, HSTACK(Amount, TAKE(CloseBal, 1, Term-1))" | |
| elegant: "OpenBal, HSTACK(Amount, DROP(CloseBal, , -1))" | |
| # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # 16. FIXED RANGE ARGUMENTS | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | fixed_range_arguments: | | | description: > | | | Convert arguments that cover multiple columns to arrays | | | rule: "Determine which argument sets the number of columns (columns) in the result. | | | Expand multi-cell, fixed range arguments (argument) using EXPAND( argument, 1, columns, 0)." | | | purpose: "Makes sure argument sizes remain dynamic ad agree with other array sizes." | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # 17. MATCH EXAMPLE OUTPUT | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | match_example_output: | | | description: > | | | match the function's output to the example's output | | | rule: "Include in the function's output all selected formula rows. Copy formats from the example to the output." | | | purpose: "Keep the users desired output." | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # TEMPLATE STRUCTURE | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | template: | | | description: > | | | Standard structure for 5G LAMBDA functions with inline documentation. | | | code: | | | | /* FUNCTION NAME: FunctionΞ» | | | DESCRIPTION:*//Description of what the function does/ | | | / REVISIONS: Date Developer Description | | | Mmm dd yyyy UserName Copyright/Initial Release | | | */ | | | FunctionΞ» = LAMBDA( | | | // Function Arguments | |
| [Required1], // Description | |
| [Required2], // Description | |
| [Optional1], // Description (default: value) | |
| // Check required arguments | |
| IF( OR( ISOMITTED(Required1), | |
| ISOMITTED(Required2) | |
| ), | | | // Display inline help text | |
| TRIM(TEXTSPLIT( | |
| "FUNCTION: βFunctionΞ»( Required1, Required2, [Optional1])ΒΆ" & | |
| "DESCRIPTION: βDescription of what the function doesΒΆ" & | | | "WEBPAGE: βhttps://5gmodelling.comΒΆ" & | | | "VERSION: βUserName, Mmm-dd-yyyyΒΆ" & | | | "PARAMETERS: βΒΆ" & | | | " Required1 β(Required) DescriptionΒΆ" & | | | " Required2 β(Required) DescriptionΒΆ" & | |
| " Optional1 β(Optional) Default: value. Description", | |
| "β", "ΒΆ")), | |
| // Main function logic | | | LET( | | | // Set Defaults | | | Optional1, IF(ISOMITTED(Optional1), DefaultValue, Optional1), | | | // Calculations | | | Result, formula, | | | Result | | | ) | | | ) | | | ); | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | # CHECKLIST | | | # βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | | | checklist: | | | - "Function name matches throughout (definition, help text FUNCTION line)" | | | - "All brackets correct in LAMBDA definition [Argument]" | | | - "Help text FUNCTION line: required args NO brackets, optional args WITH brackets" | | | - "All & concatenation operators present between help text lines" | | | - "TEXTSPLIT delimiters correct: "β", "ΒΆ" (column, row)" | | | - "All parentheses balanced (TEXTSPLIT, TRIM, IF, LET, LAMBDA)" | | | - "Boolean values uppercase: FALSE, TRUE" | | | - "Function names uppercase: AVERAGE, INDEX, SCAN, REDUCE" | | | - "Multi-row descriptions use leading "β" for continuation lines" | | | - "Optional params have default value logic in LET section" | | | - "User's name appears in REVISIONS and VERSION" | | | - "Current date used in REVISIONS and VERSION" | | | - "No angle brackets (<>) in final output" | | | - "If multiple formulas selected, all are included in the LAMBDA" | | | - "Follow input cell detection rules for passthrough references" | | | - "Use SEQUENCE(1, n) for horizontal arrays in SCAN operations" | | | - "Prefer DROP over TAKE when removing elements from array" |