Check every important calculation

Verification should be independent enough to catch the original mistake. Asking the same model, in the same conversation, “Are you sure?” may produce reassurance rather than evidence. Use a ladder:

1. Inspect the formula: Write the mathematical definition in symbols or plain language.
2. Inspect the included data: List filters, date bounds, groups, and excluded rows.
3. Recalculate a small sample manually: Choose several rows with known outcomes.
4. Reproduce in another method: Use a spreadsheet pivot, SQL query, calculator, or reviewed script.
5. Reconcile totals: Compare grouped results with trusted source totals.
6. Test edge cases: Include zero denominators, missing values, boundaries, and duplicates.

The higher the consequence, the stronger and more independent the check should be.

Many errors happen before the formula. A join can duplicate rows, a filter can remove a group, or an aggregation can mix daily and monthly records. Confirm row counts after every important transformation.

For a join, record:

• row count before and after;
• uniqueness of each join key;
• unmatched rows on both sides;
• whether the relationship is one-to-one, one-to-many, or many-to-many;
• expected effect on totals.

Suppose 1,000 orders join to a customer table and become 1,180 rows. If the customer key should be unique, stop. Summing revenue now may count 180 orders more than once. AI may write syntactically valid join code without understanding that duplication violates the business grain.

For a total, verify sign conventions, currency, refunds, and duplicates. Reconcile the result with an authoritative report for the same period and scope. Differences should be explained, not rounded away.

For an average, ask which average:

• arithmetic mean;
• median;
• weighted mean;
• average of group averages;
• ratio of total numerator to total denominator.

These are not interchangeable. If Store A has 10 sales at $100 and Store B has 1,000 sales at $20, the unweighted average of store averages is $60, while the transaction-weighted average is about $20.79. State which quantity answers the question.

For a rate, independently sum the numerator and denominator. Confirm that both cover the same population and period. Decide how repeat events and zero denominators behave. A model should return undefined or a flagged value when division has no meaningful denominator, not quietly return zero.

Percentage change is:

(new value - old value) / old value × 100

Check the baseline. A move from 2 to 4 is a 100% increase, but the small counts matter. A move from -2 to 2 does not have an ordinary percentage-change interpretation. A baseline of zero makes the formula undefined.

Distinguish percentage points from percent change. If a conversion rate moves from 20% to 25%, it rises by 5 percentage points and by 25% relative to the original rate. Both can be useful, but they communicate different magnitudes.

For year-over-year comparisons, use matched periods. Comparing six complete months this year with twelve months last year creates a predictable false decline. Check leap days, fiscal calendars, partial current periods, and timezone cutoffs.

Choose manual checks deliberately:

• one ordinary row;
• minimum and maximum values;
• a missing value;
• a boundary date;
• each major status or category;
• a duplicated key;
• a zero or negative amount;
• one row included and one excluded by each major filter.

For grouped output, select one group and reproduce its result from raw rows. If the analysis contains 200 categories, you do not need to hand-calculate all 200, but random and risk-based spot checks should agree exactly.

Ask AI to produce an audit table containing raw key, transformed values, inclusion flag, exclusion reason, numerator contribution, and denominator contribution. The audit table makes hidden logic visible.

An invariant is a rule that should always hold. Examples:

• subgroup counts sum to the overall count when groups are exclusive and complete;
• percentages sum to 100% when categories partition the whole;
• resolved date is not earlier than opened date;
• net revenue equals gross revenue minus refunds;
• a probability lies between 0 and 1;
• unique customer count cannot exceed transaction count when each transaction has one customer.

Add automated assertions where possible. Also check magnitude. If a small region suddenly accounts for 80% of global revenue, investigate units, duplicated joins, currency conversion, and source changes before celebrating the insight.

Reasonableness is not proof. Unexpected findings can be real. It is a signal to gather stronger evidence.

For every decision-critical metric, save:

• plain-language definition;
• exact formula or query;
• source table version;
• filters and exclusions;
• grouping level;
• null and duplicate rules;
• result and timestamp;
• independent check method;
• checker and outcome.

If AI changed code after an error, rerun all dependent checks. Do not verify one corrected cell while leaving downstream charts based on the old output.

Requesting code, formulas, and intermediate tables helps you inspect the process. It does not make the process correct. Generated explanations may describe a formula that differs from the executed code. Verify the actual artifact and output.

When direct reproduction is impossible because a tool hides its calculation, do not use that tool for a consequential metric. Export the data or move the calculation to an environment where logic can be reviewed.