Join for free

Refactor repeated Excel calculations with LET

Author: AILesson6 min setupTested with:ChatGPTReviewed: 2026-08-28

Quick answer

Name repeated expressions once, preserve workbook behavior, and verify that the refactor is equivalent. Provide: Current formula, Intended behavior and workbook context, Compatibility and test cases. Expected result: A readable LET formula with dependency map, equivalence tests, and compatibility fallback.

1

Add your context

Your text stays in this browser. AILesson Prompts does not send it to a model or server.

2

Your prompt

Unfilled fields remain visible as placeholders, so you can still copy and edit the prompt

Refactor the Excel formula with LET only where naming an expression improves reuse, readability, or calculation cost.

Exact formula and first cell:
[formula]

Headers, names, repeated operations, blank/error behavior, copying, and refactor goal:
[intent]

Versions/platforms, separators, performance, samples, and expected edge results:
[environment]

Parse the formula before changing it. Identify repeated calculations, stable inputs, intermediate values, volatile or array operations, short-circuit assumptions, and error boundaries. Create valid Excel variable names that communicate business meaning, avoid cell-like names and name collisions, and define each value before use. Preserve reference anchoring, evaluation meaning, blank/text/date behavior, error handling, and final type; do not hide a defect merely to produce a cleaner LET. If LET is unsupported in any required environment, provide the original or another compatible fallback. Return: behavior summary; repetition and dependency map; refactored formula; name-by-name explanation; exact changes and preserved behavior; compatibility note; performance claim limited to what can be inferred; and equivalence tests covering normal, blank, zero, error, boundary, and copy-down cases.
Private by defaultPrompt assembly happens locally in your browser. Avoid placing confidential information into any AI service unless your organization allows it.

From input to outcome

A worked example

See how concrete context turns this recipe into a usable result

Actual input

Current formula
In Sales!H2: =IF(B2="","",IFERROR(IF(XLOOKUP(B2,Products!$A$2:$A$2000,Products!$D$2:$D$2000,0)>0,ROUND(E2*XLOOKUP(B2,Products!$A$2:$A$2000,Products!$D$2:$D$2000,0),2),0),"Review"))
Intended behavior and workbook context
B is Product ID, E is quantity, H is line value. Products A is unique Product ID and D is unit price. Blank product ID returns blank; unknown ID should return Review, not zero. A real zero price returns 0. Formula copies down 20,000 rows. XLOOKUP is repeated twice. Preserve fixed source ranges and relative B/E. Existing formula may have a defect because XLOOKUP's not-found argument is 0, which could make unknown IDs look like a real zero price.
Compatibility and test cases
Microsoft 365 desktop and web only; LET and XLOOKUP supported. Comma separators. Tests: P-10, quantity 3, price 12.50 -> 37.50; P-FREE, quantity 2, price 0 -> 0; unknown P-99 -> Review; blank B -> blank; quantity text ‘three’ -> Review; copy to row 3 uses B3/E3. No measured performance baseline.

Example output

Behavior audit: the current formula repeats the same exact lookup twice, but it does not meet the stated unknown-ID rule. Its XLOOKUP returns 0 when no key is found, and the outer comparison then treats that as a legitimate zero price. A behavior-preserving LET would preserve this defect, so the correction must be called out separately.

Corrected LET formula for H2: =IF(B2="","",LET(productId,B2,quantity,E2,unitPrice,XLOOKUP(productId,Products!$A$2:$A$2000,Products!$D$2:$D$2000,NA(),0),IFERROR(IF(unitPrice>0,ROUND(quantity*unitPrice,2),0),"Review"))). productId and quantity document relative row inputs; unitPrice performs one exact lookup against fixed source ranges. NA() ensures an unknown key reaches IFERROR, while an actual numeric zero still returns 0. The intentional behavior change is unknown ID: 0 → Review. Blank, real zero, rounding, and input-error behavior are preserved.

Compatibility: valid for the stated Microsoft 365 environments; retain the corrected non-LET form only if a future recipient lacks LET. Performance may improve because one lookup replaces two per nonblank row, but no runtime percentage can be claimed without measuring the workbook.

Equivalence/correction tests: P-10 ×3 → 37.50 in corrected old and LET versions; P-FREE ×2 → 0; P-99 → Review (deliberate fix); blank ID → blank without lookup; quantity “three” with valid positive price → Review; copy to H3 changes productId/quantity to B3/E3 while Products ranges remain fixed. Also verify duplicate product IDs separately before trusting any single-result lookup.

Why this works

  1. 1

    A dependency map prevents repeated text from being merged when the references or error boundaries are not actually equivalent.

  2. 2

    Equivalence tests make readability improvements accountable to unchanged workbook results.

Check the result

  • Does each LET name represent one repeated or conceptually important value and obey Excel naming rules?

  • Are absolute, mixed, structured, and relative references preserved when copied?

  • Do original and refactored formulas match on normal and edge-case test rows?

Use it with confidence

Frequently asked questions

Practical answers about when to use this recipe, what to provide, and where human review still matters

What should I prepare before using “Refactor repeated Excel calculations with LET”?

For “Refactor repeated Excel calculations with LET,” prepare Current formula, Intended behavior and workbook context, and Compatibility and test cases. Replace placeholders only with information you can verify. If a detail is unknown, preserve that uncertainty explicitly instead of asking the model to infer it.

When is the “Refactor repeated Excel calculations with LET” result not ready to use?

The result is not ready if it does not yet deliver the stated outcome—A readable LET formula with dependency map, equivalence tests, and compatibility fallback—from the supplied evidence, or if it relies on unresolved assumptions, missing approvals, or invented details. Use the checks as release gates: revise the source inputs or assign a named, authorized reviewer instead of polishing an unsupported output.

Which AI tools have recorded tests for “Refactor repeated Excel calculations with LET”?

The published test record for “Refactor repeated Excel calculations with LET” lists ChatGPT as of 2026-08-28. This confirms recorded runs, not guaranteed compatibility or identical results in later product versions. For another tool or version, keep every constraint visible and repeat the result checks before use.

More ways to explore

Where this recipe fits

Text promptsExcel & spreadsheetsFor learners & educators

Keep the work moving