CHatmaker · August 5, 2026 21:45
diff --git a/convert-to-5g b/convert-to-5g
 ---
 name: convert-to-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.
 ---
  metadata:
    version: 1.0.5
    tags:
      - excel
      - lambda
      - afe
      - excel labs
      - named functions
      - documentation
      - 5g modeling
  triggers:
    - Make 5g
    - Make LAMBDA
    - Convert to 5g
    - Convert to LAMBDA

  workflow:
    description:
      Step-by-step process to convert a formula into a 5G LAMBDA function.
    steps:
    - step 0: Ask preferred language
      actions:
        - Detect the workbook application culture/locale setting.
        - Identify the most likely language from the regional setting.
        - Ask: "Which language would you prefer to use?" in the regional setting's likely language.
        - Display options as a numbered list to include the regional setting's likely language plus:
            - Español
            - Français
            - Deutsch
            - Italiano
            - Português
            - 中文
            - 日本語
            - 한국어
            - Other — type the language you want to use.
        - Switch to the selected language and use for the remaining steps and worksheet entries.
      - step: 1
        action: Ask for user's name
        prompt: "What name shall I use for revision documentation and version information?"
        usage: Used in REVISIONS section and VERSION line
      - step: 2
        action: Ask for formula
        prompt: "What formula would you like to convert into a 5G LAMBDA function? Select cells containing formula and when finished, type C and hit ENTER to continue."
        usage: Identifies parameters and core logic
      - step: 3
        action: Analyze the selected cells and their surroundings. Does this appear to be part of a corkscrew or other dynamic array? 
 	If so, where the formulas' precedents reference the anchor cell of a dynamic array, reference the entire array in the arguments.
      - step: 4
        action: Ask about optional parameters
        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: 5
        action: 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: 6
        action: Convert selected cells to a 5g function. Include include inline help text as show in the template.
      - step: 7
        action: Add to names collection
        description: Add the 5g LAMBDA function to the workbook's names collection
      - step: 8
        action: Test the function
        description: Find an empty space below the selected cells. In the same column as the selected cells, place the 5g function with the same inputs (adjusted for dynamic arrays as needed) as the original example. 
      - step: 9
        action: Compare results
        description: Below the 5g function and in the same columns, compare the 5g function's output to the original example to verify correctness.
      - step: 10
        action: Display the 5g source with comments in the chat window - NOT in the worksheet.

  strict execution rules:
    - Do not scan the workbook before asking the first question
    - Ask questions one at a time and wait for response before continuing.
    - When displaying options, display them as numbered lists.
    - Use cell Styles for formatting where possible. Keep worksheets clean and professional by not applying colors independently.
    - Remove gridlines from all worksheets. Do not add borders and do not use wrapped rows.
    - Use the function help text as authoritative for defaults and validation lists; do not infer ranges.
    - If a function fails, do not try to fix it.
    - When adding data validation, put the function argument name in the Input Message Title and the argument help text description in the Input Message box.
    - Start table names with "tbl" followed by a very short name. Use abbreviations where practical.
    - Apply the default table style to all tables. Do not apply additional formatting to table headers.

  rules:
    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]"    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"
    date_handling:
      description:
        Use the current date for the REVISIONS section and VERSION line.
      format: "Mmm-dd-yyyy"
      example: "May-15-2026"
    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))"
    dynamic_array_function_detection
        rule: If selected formulas repeat to the right with relative references and depend on timeline-style horizontal arrays/spills, convert the block as one period of a horizontal dynamic-array model and make the LAMBDA spill across the full timeline.
    dynamic_array_inputs:
      description: 
        When converting selected formulas to a 5G LAMBDA, inspect every referenced input cell before defining parameters. If an input cell is part of a spill range or is the anchor of a dynamic array, use the spilled-range reference with `#` as the function argument source (for example, `G23#` instead of `G23`). When any selected formula depends on dynamic-array inputs, design the resulting LAMBDA to return a dynamic array that matches the intended spill shape of the example output, rather than a single-period scalar result.
      handling:
        - Check each referenced input cell for spill behavior before classifying it as scalar.
        - If the input is a dynamic-array anchor, pass/reference it with the `#` suffix.
        - If the input cell lies inside a spill range but is not the anchor, trace back to the spill anchor and use the anchor with `#`.
        - Preserve the selected formula output shape, but expand it to a dynamic-array calculation when the referenced inputs are dynamic arrays.
        - For horizontal time-series models, prefer horizontal array logic using `SEQUENCE(1, n)`, `SCAN`, `HSTACK`, `VSTACK`, `DROP`, and `INDEX` as needed.
        - Avoid hard-coding a single period reference when the source input spills across periods.
    excel_syntax:
      description: >
        Follow standard Excel conventions for function names and booleans.
      booleans: "Uppercase: FALSE, TRUE"
      function_names: "Uppercase: AVERAGE(), INDEX(), SCAN(), REDUCE(), PMT()"
    fixed_range_arguments:
      description: Convert arguments covering multiple columns to arrays.
      rule: Expand multi-cell, fixed range arguments using EXPAND(argument, 1, columns, 0)
      purpose: Keeps argument sizes dynamic and consistent.
    horizontal_sequence_scan:
      description: >
        When using SCAN or REDUCE 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)),
    index_behavior:
      description: For single-row arrays, INDEX interprets the second argument as column number.
      rule: When array is a single row, INDEX(array, n) returns the nth column value
    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)"
    let_step_results:
      description: Name the last calculation in the LET Function. Use that name as the last step.
    match_example_output:
      description: Match function output to example output.
      rule: Include all selected formula rows in function output. Copy formats from example.
      purpose: Keep user's desired output.
    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
    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.",
    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),
    string_concatenation:
      description: >
        Ensure all help text lines are properly concatenated with the & operator.
      rule: "Every line except the last must end with &"
    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: "¶"

 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:
  - "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 ends"
	---
	name: convert-to-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.
	---
	metadata:
	version: 1.0.5
	tags:
	- excel
	- lambda
	- afe
	- excel labs
	- named functions
	- documentation
	- 5g modeling
	triggers:
	- Make 5g
	- Make LAMBDA
	- Convert to 5g
	- Convert to LAMBDA

	workflow:
	description:
	Step-by-step process to convert a formula into a 5G LAMBDA function.
	steps:
	- step 0: Ask preferred language
	actions:
	- Detect the workbook application culture/locale setting.
	- Identify the most likely language from the regional setting.
	- Ask: "Which language would you prefer to use?" in the regional setting's likely language.
	- Display options as a numbered list to include the regional setting's likely language plus:
	- Español
	- Français
	- Deutsch
	- Italiano
	- Português
	- 中文
	- 日本語
	- 한국어
	- Other — type the language you want to use.
	- Switch to the selected language and use for the remaining steps and worksheet entries.
	- step: 1
	action: Ask for user's name
	prompt: "What name shall I use for revision documentation and version information?"
	usage: Used in REVISIONS section and VERSION line
	- step: 2
	action: Ask for formula
	prompt: "What formula would you like to convert into a 5G LAMBDA function? Select cells containing formula and when finished, type C and hit ENTER to continue."
	usage: Identifies parameters and core logic
	- step: 3
	action: Analyze the selected cells and their surroundings. Does this appear to be part of a corkscrew or other dynamic array?
	If so, where the formulas' precedents reference the anchor cell of a dynamic array, reference the entire array in the arguments.
	- step: 4
	action: Ask about optional parameters
	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: 5
	action: 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: 6
	action: Convert selected cells to a 5g function. Include include inline help text as show in the template.
	- step: 7
	action: Add to names collection
	description: Add the 5g LAMBDA function to the workbook's names collection
	- step: 8
	action: Test the function
	description: Find an empty space below the selected cells. In the same column as the selected cells, place the 5g function with the same inputs (adjusted for dynamic arrays as needed) as the original example.
	- step: 9
	action: Compare results
	description: Below the 5g function and in the same columns, compare the 5g function's output to the original example to verify correctness.
	- step: 10
	action: Display the 5g source with comments in the chat window - NOT in the worksheet.

	strict execution rules:
	- Do not scan the workbook before asking the first question
	- Ask questions one at a time and wait for response before continuing.
	- When displaying options, display them as numbered lists.
	- Use cell Styles for formatting where possible. Keep worksheets clean and professional by not applying colors independently.
	- Remove gridlines from all worksheets. Do not add borders and do not use wrapped rows.
	- Use the function help text as authoritative for defaults and validation lists; do not infer ranges.
	- If a function fails, do not try to fix it.
	- When adding data validation, put the function argument name in the Input Message Title and the argument help text description in the Input Message box.
	- Start table names with "tbl" followed by a very short name. Use abbreviations where practical.
	- Apply the default table style to all tables. Do not apply additional formatting to table headers.

	rules:
	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]" 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"
	date_handling:
	description:
	Use the current date for the REVISIONS section and VERSION line.
	format: "Mmm-dd-yyyy"
	example: "May-15-2026"
	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))"
	dynamic_array_function_detection
	rule: If selected formulas repeat to the right with relative references and depend on timeline-style horizontal arrays/spills, convert the block as one period of a horizontal dynamic-array model and make the LAMBDA spill across the full timeline.
	dynamic_array_inputs:
	description:
	When converting selected formulas to a 5G LAMBDA, inspect every referenced input cell before defining parameters. If an input cell is part of a spill range or is the anchor of a dynamic array, use the spilled-range reference with `#` as the function argument source (for example, `G23#` instead of `G23`). When any selected formula depends on dynamic-array inputs, design the resulting LAMBDA to return a dynamic array that matches the intended spill shape of the example output, rather than a single-period scalar result.
	handling:
	- Check each referenced input cell for spill behavior before classifying it as scalar.
	- If the input is a dynamic-array anchor, pass/reference it with the `#` suffix.
	- If the input cell lies inside a spill range but is not the anchor, trace back to the spill anchor and use the anchor with `#`.
	- Preserve the selected formula output shape, but expand it to a dynamic-array calculation when the referenced inputs are dynamic arrays.
	- For horizontal time-series models, prefer horizontal array logic using `SEQUENCE(1, n)`, `SCAN`, `HSTACK`, `VSTACK`, `DROP`, and `INDEX` as needed.
	- Avoid hard-coding a single period reference when the source input spills across periods.
	excel_syntax:
	description: >
	Follow standard Excel conventions for function names and booleans.
	booleans: "Uppercase: FALSE, TRUE"
	function_names: "Uppercase: AVERAGE(), INDEX(), SCAN(), REDUCE(), PMT()"
	fixed_range_arguments:
	description: Convert arguments covering multiple columns to arrays.
	rule: Expand multi-cell, fixed range arguments using EXPAND(argument, 1, columns, 0)
	purpose: Keeps argument sizes dynamic and consistent.
	horizontal_sequence_scan:
	description: >
	When using SCAN or REDUCE 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)),
	index_behavior:
	description: For single-row arrays, INDEX interprets the second argument as column number.
	rule: When array is a single row, INDEX(array, n) returns the nth column value
	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)"
	let_step_results:
	description: Name the last calculation in the LET Function. Use that name as the last step.
	match_example_output:
	description: Match function output to example output.
	rule: Include all selected formula rows in function output. Copy formats from example.
	purpose: Keep user's desired output.
	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
	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.",
	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),
	string_concatenation:
	description: >
	Ensure all help text lines are properly concatenated with the & operator.
	rule: "Every line except the last must end with &"
	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: "¶"

	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:
	- "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 ends"
No results found