Stop copying the same formula into cell after cell. Describe the calculation you repeat constantly and ChatGPT writes the full LAMBDA function — clear parameter names, a test formula, and the exact text to paste into Name Manager. Microsoft 365 or Excel 2024.
I want to create a reusable LAMBDA function in Microsoft 365 or Excel 2024. Here is the calculation I repeat: [describe the calculation and all rules in plain English] Inputs: [list each input and what it represents] Valid input rules: [list any required ranges, whole-number requirements, allowed values, or other validation rules] For invalid inputs: [state what the function should return, or write "recommend an appropriate validation result"] EXCEL RELIABILITY RULES: - Do not invent workbook facts, business rules, inputs, or expected behavior not stated here. - If required information is missing, say exactly what is missing instead of guessing. - Do not rely on uncertain Excel evaluation behavior or short-circuit assumptions. Structure formulas so unsafe operations are reached only after required type checks. - Preserve existing behavior unless I explicitly ask you to change it. - Verify the final formula against every sample input and expected result I provide. - Check only edge cases relevant to the stated requirements. - Flag Excel-version compatibility when relevant; do not guess version support. Build rule: Implement every stated validation rule and no others. If numeric operations such as INT() could error on text input, use a separate type-validation gate before those operations. Please: 1. Write the LAMBDA formula with clear parameter names. 2. Do not invent business rules that I have not provided. 3. Add only the input validation described above. 4. Give me an inline test formula that invokes the LAMBDA definition directly and can run before Name Manager registration. 5. Show the expected result of that test and verify the calculation. 6. Give me the exact LAMBDA definition to paste into Name Manager. 7. Give me one example of how to call the named function after registration.
Describe the logic in plain English — no need to know LAMBDA syntax yourself. Paste the prompt into ChatGPT.
My Excel LAMBDA function is returning an error or the wrong result. Excel version: [Microsoft 365 / Excel 2024] Function name: [enter the function name] LAMBDA definition from Name Manager: [paste the exact definition] Worksheet call: [paste the exact call] Actual input values: [list each value and what it represents] Actual result or Excel error: [paste the result or exact error] Expected result: [enter the expected result] EXCEL RELIABILITY RULES: - Do not invent workbook facts, business rules, inputs, or expected behavior not stated here. - If required information is missing, say exactly what is missing instead of guessing. - Do not rely on uncertain Excel evaluation behavior or short-circuit assumptions. - Preserve existing behavior unless I explicitly ask you to change it. - Verify the final formula against every sample input and expected result I provide. - Check only edge cases relevant to the stated requirements. - Flag Excel-version compatibility when relevant; do not guess version support. Debugging rule: Identify the cause before changing the formula. Do not fix behavior that was not reported as broken. Please: 1. State what the current LAMBDA definition does. 2. Check the definition, parameter order, worksheet call, and supplied input values. 3. Identify the exact cause if it can be determined from the information provided. 4. If it cannot be determined with certainty, say what information is missing instead of guessing. 5. Verify the expected result mathematically. 6. Give only the correction that is actually required. 7. State clearly whether the LAMBDA definition itself needs to change.
Include the actual call and inputs, not just the LAMBDA definition — errors often depend on what's passed in.
Here is an Excel formula I use in many cells: [paste the exact formula] Excel version: [Microsoft 365 / Excel 2024] The formula is currently in: [cell, e.g. D2] The referenced cells/ranges mean: [list each important reference and what it represents] Sample values and expected result: [provide at least one concrete test case] Convert this formula into a reusable LAMBDA function with a clear name. EXCEL RELIABILITY RULES: - Do not invent workbook facts, business rules, inputs, or expected behavior not stated here. - If required information is missing, say exactly what is missing instead of guessing. - Do not rely on uncertain Excel evaluation behavior or short-circuit assumptions. - Preserve existing behavior unless I explicitly ask you to change it. - Verify the final formula against every sample input and expected result I provide. - Check only edge cases relevant to the stated requirements. - Flag Excel-version compatibility when relevant; do not guess version support. Conversion rule: The LAMBDA must preserve the original formula's behavior exactly, including existing errors and edge cases. Do not add validation or change behavior unless explicitly asked. Please: 1. State briefly what the original formula does. 2. Decide which worksheet references should become LAMBDA parameters. 3. Use clear parameter names based only on the information provided. 4. Give the exact LAMBDA definition to paste into Name Manager. 5. Give an inline test formula using the supplied sample values. 6. The inline test must invoke the LAMBDA definition directly and must work BEFORE the function is registered in Name Manager. Do not use the named function for this test. 7. Verify that the inline test matches the expected result. 8. Give an example worksheet call after registration. 9. Confirm that the converted LAMBDA preserves the original formula's stated edge-case behavior. 10. Do not add extra validation or error handling that is not present in the original formula.
Good for cleaning up a workbook where the same complex formula is copied across dozens of cells.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Dept | Score | Salary | Bonus |
| 2 | Adams, J. | Sales | 5 | £48,000 | £9,600 |
| 3 | Bell, K. | Finance | 4 | £52,000 | £5,200 |
| 4 | Chen, L. | HR | 3 | £38,000 | £1,900 |
| 5 | Davis, M. | Ops | 2 | £41,000 | £0 |
| 6 | Evans, P. | Sales | 1 | £44,500 | £0 |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Dept | Score | Salary | Bonus |
| 2 | Adams, J. | Sales | 5 | £48,000 | £9,600 |
| 3 | Bell, K. | Finance | 4 | £52,000 | £5,200 |
| 4 | Chen, L. | HR | 3 | £38,000 | £1,900 |
| 5 | Davis, M. | Ops | 2 | £41,000 | £0 |
| 6 | Evans, P. | Sales | 1 | £44,500 | £0 |
| 7 | Total bonus payout | £16,700 | |||
=LAMBDA(score,salary,
IF(OR(NOT(ISNUMBER(score)),NOT(ISNUMBER(salary))),
"CHECK INPUT",
IF(OR(score<1,score>5,score<>INT(score),salary<0),
"CHECK INPUT",
IFS(score=5,salary*20%,
score=4,salary*10%,
score=3,salary*5%,
TRUE,0))))
Inline test:
=LAMBDA(score,salary,IF(OR(NOT(ISNUMBER(score)),NOT(ISNUMBER(salary))),"CHECK INPUT",IF(OR(score<1,score>5,score<>INT(score),salary<0),"CHECK INPUT",IFS(score=5,salary*20%,score=4,salary*10%,score=3,salary*5%,TRUE,0))))(5,48000)
→ £9,600 ✓
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Revenue | Costs | Net Margin |
| 2 | Widget Pro | £84,000 | £63,000 | −33.3% |
| 3 | Widget A | £52,000 | £41,600 | −25.0% |
| 4 | Widget B | £31,000 | £26,350 | −17.6% |
NET_MARGIN is defined as LAMBDA(revenue,costs,...), but the worksheet call passes Costs first and Revenue second: =NET_MARGIN(C2,B2).| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Revenue | Costs | Net Margin |
| 2 | Widget Pro | £84,000 | £63,000 | 25.0% |
| 3 | Widget A | £52,000 | £41,600 | 20.0% |
| 4 | Widget B | £31,000 | £26,350 | 15.0% |
Definition: =LAMBDA(revenue,costs, IF(revenue=0,0,(revenue-costs)/revenue)) Wrong: =NET_MARGIN(C2,B2) Fixed: =NET_MARGIN(B2,C2) Widget Pro: (84,000−63,000)/84,000 = 25.0% ✓
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Revenue | Costs | Net Margin |
| 2 | Widget Pro | £84,000 | £63,000 | =IF(B2=0,0,(B2-C2)/B2) |
| 3 | Widget A | £52,000 | £41,600 | =IF(B3=0,0,(B3-C3)/B3) |
| 4 | Widget B | £31,000 | £26,350 | =IF(B4=0,0,(B4-C4)/B4) |
| 5 | … | … | … | same logic copied down |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Revenue | Costs | Net Margin |
| 2 | Widget Pro | £84,000 | £63,000 | 25.0% |
| 3 | Widget A | £52,000 | £41,600 | 20.0% |
| 4 | Widget B | £31,000 | £26,350 | 15.0% |
=LAMBDA(revenue,costs, IF(revenue=0,0,(revenue-costs)/revenue)) Use in the sheet: =NET_MARGIN(B2,C2) Update the LAMBDA once in Name Manager; every call uses the same logic. ✓
Look for any formula you write more than a few times — bonus calculations, margin percentages, date conversions, conditional flags. Copied into more than three cells is a strong LAMBDA candidate.
Explain the logic in plain English: "Calculate net margin as revenue minus costs divided by revenue. Return 0 if revenue is zero." ChatGPT translates this into correct LAMBDA syntax with named parameters.
Test the LAMBDA in a cell by invoking it immediately, for example =LAMBDA(x,x+1)(1). A bare LAMBDA definition entered in a cell without a call returns #CALC!. If the result is wrong, send ChatGPT the definition, call, inputs, actual result and expected result.
Go to Formulas → Name Manager → New. Enter a clear workbook name (for example NET_MARGIN), paste the LAMBDA into "Refers to", and click OK. ALL_CAPS is just a naming convention, not an Excel requirement. The function is now available workbook-wide.
Type =NET_MARGIN(B2,C2) in any cell — Excel treats it exactly like a built-in function. Test with at least three different input combinations to confirm it handles edge cases correctly.
LET, which lets you define named variables inside a formula. This keeps multi-step calculations readable even as the logic grows more complex. Ask ChatGPT to use LET if the formula body is getting long.