Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

AI in Marketing › AI Applications in Marketing

Checking AI-Written Spreadsheet Formulas and Calculated Fields

A routine for checking the spreadsheet formulas and dashboard calculated fields an AI tool writes: definitions, ranges, lookups, blanks, dates and a hand total.
Tutorialintermediate10 min readUpdated

Why a formula that returns a number can still be wrong

A formula is a definition written in a notation. "Rebooking rate" means something specific to the business: which visits count, which later bookings count as rebookings, over what window, for which locations. A model asked for the formula fills each of those choices with the most common pattern it has seen, and the formula does not say which choices it made. The AI for analysis and reporting guide puts the rule plainly: every AI-produced number is traced to its source before it is used.

The model also cannot see the sheet. It sees the description in the prompt. It does not know that the visits tab gains rows every week, that one location code was reused when a branch reopened, that cancelled visits are left as blank rows, or that the export writes dates as text. Those facts decide whether a formula is right, and they live in the sheet and in the heads of the people who maintain it.

And the failures are silent. A syntax error is caught at once, because the spreadsheet or Looker Studio refuses the formula. A range one row short, a near match in a lookup or a blank counted as zero produces a figure that is close enough to believe. The table lists the common ones.

FailureWhat it looks likeWhere the routine catches it
Wrong definitionA tidy answer to a slightly different questionWriting the definition before asking
Short or sliding rangeNew rows left out, or references that shift when the formula is copied down or acrossChecking ranges and references
Wrong lookup matchThe nearest key, the first of several duplicates, or a silent blank for a missing keyChecking lookups against the key list
Blanks and text as zeroRates diluted by empty rows, or text values dropped from a sumChecking how blanks, zeros, text and errors are treated
Wrong periodA month cut at the wrong date, or text dates sorted as wordsChecking date boundaries
Average of ratiosA rate that weights a tiny location the same as a large oneChecking the level a ratio is calculated at

Before you start

  • The metric's definition, or the place it should live. The metrics layer and definitions guide sets out formula, grain, filters and owner; if the metric is part of a KPI tree, note the levels above and below it.
  • A description of the sheet or data source: tab or source names, column names, what one row represents, formats, and the quirks you know about.
  • Invented sample rows that look like the real ones, including the awkward cases: a blank, a duplicate key, a text date, a location with very few visits.
  • A known total for the same scope from a Source of Truth such as the booking system, the ad platform or finance.
  • An approved AI tool. Follow the data minimisation tutorial: column names and invented rows, never customer records.
  • For a dashboard field, edit access to the data source in Looker Studio and the calculated fields guide open beside you.

The steps

  1. Write the metric's definition before asking

    Write one sentence for the metric and then each choice inside it: what counts in the numerator, what counts in the denominator, which rows are excluded, which date marks the event, what the period is and at what level the result is reported. "Rebooking rate" might become: visits completed in the month at which the customer booked another visit within a stated window, divided by visits completed in the month, excluding cancellations and staff test bookings, reported per location and for the whole business. Where a governed definition exists, quote it. Every later step checks the formula against this sentence, so it comes first.

  2. Describe the sheet or data source exactly, with column names and invented sample rows

    Give the model the definition, the tab or data source name, each column with its type and format, what one row represents, and a handful of invented rows that include the awkward cases. Say where the data ends today and that new rows will be added. Ask for the formula, a plain explanation of each part, and a list of every assumption it made. For a Looker Studio field, say whether it will be a data source field or a chart-specific one, since the calculated fields guide treats the source as the default home for a metric that several charts use.

  3. Read the formula piece by piece

    Before using the result, split the formula into its parts and say in words what each does: which range it reads, which condition it applies, which value it looks up, what it divides by. Mark each part as matching the definition, differing from it or unclear. Compare the model's explanation with the formula itself; an explanation can describe the formula the model meant to write. A long formula that nobody can read aloud is a risk in itself; ask for it in smaller helper columns or fields that can each be checked.

  4. Check that ranges and references cover every row and hold when copied

    Find every range in the formula and check where it starts and ends. A range written for the sample rows in the prompt often stops at the last invented row, so new data is left out without warning. Then check what happens when the formula is copied down a column or across a row: references meant to stay fixed, such as a lookup table or a target cell, must not move, and references meant to follow the row must. Copy the formula into a few rows and read where each copy points. In Looker Studio the equivalent check is the field's scope: a chart field is visible only in its chart, a source field everywhere the source is used.

  5. Check lookups for wrong matches and missing keys

    Lookups fail in three quiet ways, as general practice across spreadsheet tools. A lookup that allows an approximate match returns the nearest key rather than an exact one, which is right for bands and wrong for codes. A lookup table with duplicate keys returns only one of them, so Deduplication of the key column matters before the lookup is trusted. A key missing from the table returns an error or a blank, and a formula wrapped to hide errors turns that into a zero or an empty cell nobody notices. Confirm the match type the definition needs, count the keys in the lookup table for duplicates, and test a key that is deliberately absent. In a Looker Studio blend, the blending tutorial applies the same thinking to join keys and join types.

  6. Check how blanks, zeros, text and errors are treated

    Decide, from the definition, what a blank row means: a visit not yet completed, a cancelled visit, missing data. Then check what the formula does with it. Some functions skip blanks and some count them; a count of rows can include empty ones; a numeric column with a few values stored as text can drop them from a sum without warning. Check division too: a denominator of zero should produce a visible blank or marker, not an error that a wrapper turns into zero and a chart then averages. Test each case with one of the awkward invented rows and read the result.

  7. Check date boundaries and the level a ratio is calculated at

    Confirm the date column the formula uses, the first and last day it includes, and whether dates are real dates or text that only looks like dates; text sorts and compares as words, so a period filter on it can include the wrong months. Then check the level. A ratio must be computed from totals at the level it is reported: total rebookings divided by total visits for the business, not the average of each location's rate. The calculated fields guide makes this the central rule for Looker Studio ratios, written as an aggregated numerator divided by an aggregated denominator, and explains the difference between row-level and aggregated formulas. State the Table Grain of the data and of the result, and confirm the formula moves between them correctly.

  8. Recompute a small sample by hand and reconcile to a known total

    Pick a few rows, or one location and one month, and work the metric out by hand from the raw values, following the written definition. Compare with what the formula returns for the same slice. Then compare the formula's overall total for a period with the known total gathered before you started. Expect small, explainable gaps such as late cancellations; write the explanation down. An unexplained gap means a step above was wrong, so go back rather than adjusting the result. Where the counts are small, read differences with the care the guide to reading small numbers recommends.

  9. Save the formula with its definition and what it was checked against

    Record the formula or field name, the definition it implements, the sheet or source it reads, the checks run, the hand recomputation and reconciliation result, the date and the person who checked it. For a dashboard, run the dashboard launch QA checklist before the field reaches a shared report. Where the same metric is computed in a warehouse table, the data tests guide describes tests that keep it honest over time. Mark the formula for re-checking when columns, sources or the definition change.

A checked formula is a small part of a governed KPI. Keeping the definition in one place, as the metrics layer guide describes, is what stops the next request to an AI tool from producing a second, slightly different version of the same number. The applications hub lists the other AI task guides. When the formula or field is ready to reach a report, the checklist for reviewing AI-written code gathers these checks into one gate. If a figure the formula produced is already in dispute, the diagnosis for AI-generated report numbers that do not match starts from the figure nobody can reproduce and finds which of its causes applies.

A dog grooming franchise checks a generated rebooking formula (illustrative)

Illustrative example — not a real result

Wexidra Dog Grooming checks a generated rebooking rate

Wexidra Dog Grooming, an invented franchise of 9 dog grooming salons, wants a monthly rebooking rate in its operations sheet and on its Looker Studio dashboard. The definition: visits completed in the month where the owner booked another visit within 8 weeks, divided by visits completed in the month, excluding cancellations.

The marketing lead describes the visits tab with invented rows and asks an AI tool for a sheet formula and a dashboard field. Both return plausible results: the dashboard shows 61% for March. The check finds 2 problems.

FindingEffectFix
The dashboard field divides rebookings by visits on each salon's row and the chart averages the 9 ratesA salon with 40 visits weighs the same as one with 610Total rebookings divided by total visits, both aggregated
The sheet's lookup of salon codes allows a near match, so code S07, missing from the salon table after a reopening, picks up S06118 visits credited to the wrong salonExact match, S07 added to the table, a test row for a missing code

Recomputed by hand for one salon, the corrected formula gives 87 rebookings from 152 visits, matching the sheet. For the whole business, March's corrected rate is 54%: 1,512 rebookings from 2,800 completed visits. The booking system reports 2,806 completed visits; the gap of 6 is late cancellations entered in April, and the note says so. The formula, the field and the reconciliation are saved beside the definition.

Common mistakes

  • Asking before defining. Without a written definition there is nothing to check the formula against.
  • Pasting the real sheet into the prompt. Column names and invented rows give the model what it needs.
  • Trusting the explanation over the formula. Read the formula itself; the explanation can describe another one.
  • Ranges that end at the sample. A formula built for the prompt's rows quietly ignores next month's.
  • Near matches on codes. Approximate matching is for bands, not identifiers.
  • Hiding errors with a wrapper. A missing key turned into a zero becomes a wrong total nobody questions.
  • Averaging rates. Report ratios computed from totals at the level shown.
  • Skipping the hand recomputation. A few rows worked out by hand catch what reading alone misses.
  • Retyping the formula in every chart. Define the metric once and reuse it.

Frequently asked questions

The AI tool's formula returns a sensible-looking number. Why check it?

Because the errors that matter in formulas rarely produce an error message. A range that misses the newest rows, a lookup that returns a near match, blanks treated as zeros or an average of rates instead of a rate of totals all return numbers that look reasonable. The only proof is reading the formula against a written definition and recomputing a small sample by hand.

Can I share the real spreadsheet with the AI tool so it writes a better formula?

Describe it instead. The model needs the column names, what one row represents, the formats and a few invented rows that look like the real ones. It does not need customer names, contact details or real transactions, and an approved tool used with invented rows produces the same formula without exposing the data.

Should the formula live in the spreadsheet or in the dashboard?

Wherever the definition is maintained once and reused. A metric that several reports depend on belongs in one governed place, such as a data source field or a metrics layer, rather than retyped in each chart or sheet. A one-off working formula in a personal sheet is fine as long as its result is not reported as an agreed figure without the same checks.

Terms used on this page

Frameworks

Also related

Sources

  1. Looker Studio Help — Looker Studio Help

Related