Explain & Fix Formula (Formula Coach)

Standard tier

Available on Standard tier and above. User supplies their own Gemini/ChatGPT API key — DVDAddin charges nothing for AI.

2 AI commands to understand and fix Excel formulas:

Command	Purpose	Ribbon button
Explain formula (Coach)	Breaks down the current cell's formula → explains each piece in detail + examples	btnFormulaExplain
Fix formula error	Detects errors (#REF!, #N/A, #DIV/0!, ...) → AI suggests fixed formulas	btnFormulaFix

Unlike the simple AI Explain (one-paragraph output), Coach mode segments the formula into sub-parts + explains each + provides value examples.

Open the dialog

DVD Addin → group AI Assistant → ▼ dropdown → Explain formula (Coach) or Fix formula error.

Command 1: Explain Coach

When to use

• Open someone else's file → complex formula you don't understand
• Learning new functions (e.g. XLOOKUP, LAMBDA, LET)
• Teaching / team sharing — copy AI's explanation to send around

Workflow

1. Click a cell with a formula (e.g. =IFERROR(VLOOKUP(B5,Sheet2!$A:$C,3,FALSE)*1.1,0))
2. DVD Addin → AI dropdown → Explain formula (Coach)
3. AI dialog shows structured output:

📋 Original formula:
   =IFERROR(VLOOKUP(B5,Sheet2!$A:$C,3,FALSE)*1.1,0)

🔍 Breakdown:

1. VLOOKUP(B5,Sheet2!$A:$C,3,FALSE)
   • Looks up the value in B5 in column A of Sheet2
   • Returns the value in the 3rd column (C) when matched exactly
   • Example: B5="AB.11110" → matches Sheet2!A7="AB.11110" → returns Sheet2!C7

2. * 1.1
   • Multiplies the VLOOKUP result by 1.1 (10% increase)
   • Could be price escalation, VAT, profit markup...

3. IFERROR(..., 0)
   • If VLOOKUP doesn't find a match → returns 0 instead of #N/A
   • Protects the formula from error propagation

💡 Result behavior:
   - B5 exists in Sheet2 → column C value × 1.1
   - B5 doesn't exist → 0

⚠️ Warning:
   - $A:$C reference may be slow if Sheet2 is large (whole-column scan)
   - Suggestion: use specific range like $A$1:$C$10000

Coach tips

• Dialog has 📋 Copy button → paste into chat / docs
• 🌐 EN button → explain in English (or switch to VI)
• ⚡ Simpler button → shorter output (1 paragraph instead of breakdown)
• Can ask follow-up in the dialog: "Can this formula be improved?" → AI suggests an optimized version

Command 2: Fix formula error

When to use

• Cell shows #N/A, #REF!, #VALUE!, #DIV/0!, #NAME?, #NULL!
• Don't understand why the formula errors
• Need a quick fix, no time to debug each piece

Workflow

1. Click the erroring cell
2. Fix formula error
3. AI dialog analyzes:

📋 Errored formula:
   =VLOOKUP(B5,Sheet2!$A:$C,4,FALSE)
   Currently returns: #REF!

🔍 Diagnosis:
   - Sheet2!$A:$C only has 3 columns (A, B, C)
   - You asked for column 4 → out of range → #REF!

🔧 Suggested fixes (3 options):

Option 1: Correct the column number
   =VLOOKUP(B5,Sheet2!$A:$C,3,FALSE)
   ↑ Change 4 → 3 (column C is the 3rd column)

Option 2: Widen the range
   =VLOOKUP(B5,Sheet2!$A:$D,4,FALSE)
   ↑ Extend to column D (if column D has data)

Option 3: Use XLOOKUP (Excel 365+)
   =XLOOKUP(B5,Sheet2!$A:$A,Sheet2!$D:$D)
   ↑ More flexible, no column counting

Click Apply under each option → the cell's formula auto-updates.

Common errors + AI suggestions

Error	Common cause	AI suggests
#N/A	VLOOKUP no exact match / missing IFERROR	Add IFERROR, switch to approximate match
#REF!	Cell ref deleted, col_index out of range	Restore ref, adjust col number
#VALUE!	Text + Number arithmetic, wrong arg type	Convert with VALUE(), NUMBERVALUE()
#DIV/0!	Divide by 0 or blank cell	Wrap in IFERROR or IF(B<>0, ...)
#NAME?	Function name typo, range name doesn't exist	Fix typo, define name
#NULL!	Range intersection doesn't exist	Check space separator, replace with comma

API key required

In Preferences → AI tab → enter API key for Gemini (free quota) or ChatGPT. Without a key → dialog shows an error.

Cost: each Explain/Fix call ≈ 500-2000 tokens → Gemini free quota suffices ~100 calls / day.

License gating

Tier	Available
Trial	✅ — full access
Standard	✅ — full access (user-supplied API key)
Pro	✅ — full access
Expired / Unactivated	🔒 Locked

TIP

Both commands moved to FREE in Wave 4.3.18 — no Std/Pro license required. Just supply your own API key.

Related

• AI Assistant — main AI Assistant (analyze, generate)
• Formula Viewer — view formulas in a range
• UDF functions — write custom Excel functions