Excel Formula Explainer

What this skill does

Takes a single Excel formula (e.g., a 200-character INDEX/MATCH chain) and produces a plain-English explanation, a dependency tree of subexpressions, and a worked example with sample inputs and outputs.

Inputs

• formula: the formula string starting with =.
• Optional workbook_path: an .xlsx file referenced by the formula, used to resolve named ranges and sample cell values.
• Optional target_audience: analyst (default) or non-technical.

Steps

1. Tokenize the formula using a small Pratt parser: function calls, references (Sheet1!A1:B10), names, operators, literals.
2. Build an AST. Each function call becomes a node with arguments as children.
3. Resolve named ranges: open the workbook with openpyxl (Python) and read defined_names.
4. For each function in the AST, emit a one-line description from a built-in lookup table (e.g., XLOOKUP(lookup, array, return_array, [if_not_found]) -> "Find lookup in array and return the matching row from return_array; if missing, return if_not_found.").
5. Walk the AST top-down and write a numbered list: step 1 is the outermost function, step N is the innermost.
6. If workbook_path is provided, evaluate sub-expressions on a sample of rows. Use formulas (Python) or xlcalculator to evaluate without Excel.
7. Render a "Worked Example" section with a small table: Input cell -> Value -> Sub-result.
8. Add a "Common Pitfalls" section: name 2-3 ways this formula can return #N/A, #REF!, or wrong results given sparse data.
9. If target_audience == non-technical, replace function-name jargon with verbs ("looks up" instead of "XLOOKUP").
10. Emit a markdown explainer.

Output

formula-explained.md with three sections: Plain-English Walkthrough, Worked Example, Common Pitfalls. Top of file shows the original formula and a tree-rendered AST.

Verification

Re-evaluate the formula with the supplied workbook (if any) and confirm the worked example's final result matches the actual cell value. Walk the AST line by line and verify every function appears in the explanation step list. If the formula contains a function the lookup table doesn't know (e.g., a custom LAMBDA), surface "unknown function" rather than guess.

Edge cases

• LAMBDA / LET formulas: parse the bound names, treat them as local variables, and inline their definitions in the walkthrough.
• Array formulas (CSE-entered): note the array context; results may differ from regular formulas.
• Volatile functions (NOW, RAND): mark them and warn the worked example may not reproduce.
• External workbook references ('[Other.xlsx]Sheet1'!A1): cannot resolve without that workbook; surface as opaque reference.