Formula Coach (Explain + Fix)
When you inherit someone else's estimate, BoQ or payment application, the slow part is never the typing — it is reading nested formulas you did not write, and working out why one cell suddenly returns #N/A. The two AI Formula Coach commands cover exactly those two jobs: one explains a formula in plain language, the other diagnoses the error and proposes a replacement formula you can write straight back into the cell.
Both live in the AI Menu of the AI group on the DVD Addin tab and use the API key you set in Preferences ▸ AI Assistant.
Commands in this group
| Command | What it does |
|---|---|
| Explain formula (Coach) | Sends the active cell's formula to the AI and shows the explanation in a panel — nothing is written to the sheet |
| Fix formula error | Diagnoses #N/A, #VALUE!, #REF!, #DIV/0!… and proposes a replacement formula with a button to apply it |
| AI Chat | The general AI panel inside Excel, for building a formula from scratch or asking follow-up questions |
The two Coach commands open the same AI Formula Coach window in two different modes. The window is modeless: select another cell on the sheet and press the analyse button again to keep going — no need to close and reopen it. The window holds:
- the Cell: line — the address being analysed, with a red badge naming the error type in fix mode;
- the original formula, read-only;
- the Explanation box — the AI answer, selectable so you can copy it;
- the Suggested formula: block — fix mode only, and only when the AI produced a replacement; its text is editable;
- the analyse button (Explain or Analyze), plus Apply to cell and Close.
Working through an unfamiliar formula
- Set the API key once — go to Preferences (Info group) → AI Assistant, choose the provider and paste your key. Without a key the Coach panel reports that the API key is missing.
- Select a cell containing a formula. If you select a range, only the first cell is analysed.
- Read it first — click DVD Addin → AI → AI Menu → Explain formula (Coach), then click Explain. The panel shows the cell address, the formula and the Explanation box; the text is selectable, so you can copy it into a report or an email.
- If the cell is failing — click Fix formula error. The Cell: line shows the address with a red badge naming the error type, and the original formula appears below it read-only.
- Click Analyze (bottom right). The answer appears in the Explanation panel, and when the AI can propose a replacement a Suggested formula: block appears — editable, so you can adjust it before applying.
- Check the argument separator (
,versus;) against your regional settings, then click Apply to cell — or Close to discard the suggestion. - Verify the result on the sheet before calling it done, especially for cells that carry money or measured quantities.
Example
In a quantity summary, cell H24 pulls a rate from the Rates sheet:
| Cell | Formula | Result |
|---|---|---|
H24 — before | =VLOOKUP(B24,Rates!A:D,4,0) | #N/A |
H24 — after | =IFERROR(VLOOKUP(TRIM(B24),Rates!A:D,4,0),0) | 1,850,000 |
Select H24 → Fix formula error → Analyze. The Explanation box points out that the item code in B24 carries a trailing space, so it never matches column A of the rate sheet; the Suggested formula block wraps the lookup value in TRIM. Click Apply to cell and the rate comes through.
In the other direction, for a correct but hard-to-read cell such as =SUMPRODUCT((Code=$B5)*(Month=C$4)*Qty), Explain formula (Coach) reports that the formula totals the quantity of the work code in B5 for the month in C4, and reminds you that the named ranges Code, Month and Qty must all span the same number of rows.
Tips and caveats
- An Internet connection and an API key set in Preferences ▸ AI Assistant are both required (Explain uses a Gemini API key); without a key, Analyze returns nothing.
- The selected cell must contain a formula (starting with
=); a value-only cell reports "The selected cell has no formula". - Apply to cell overwrites the original formula immediately — note or back up the old formula first.
- The Suggested formula block only appears when the AI actually produced a replacement; sometimes you get an explanation and nothing else.
- Answers are AI-generated: check them against the cell's real behaviour, particularly for cost and accepted-quantity formulas.
- The formula text is sent to an external AI service (other cell data is not sent). The AI Chat panel additionally requires an active DVDAddin account.
See also
- Formula Tools — inspecting formulas without AI: token breakdown, lookup tracing, dependents.
- AI Assistant — the general chat panel and the other AI commands in the group.
- AI, network & shape functions —
dvdAIExplain, the worksheet-function version of formula explanation (it writes the answer into a cell).