AI Spreadsheet Formulas: Define What the Average Counts

An AI assistant gives you a spreadsheet formula. You paste it in, a number appears, and there is no error message. Has the calculation passed? Only partly. The formula may be valid while counting a different set of records from the one you intended.

Consider a creator tracking how long completed customer requests take to handle. Should a recorded zero count? What about a blank where the time was never entered? Before asking AI to calculate the average, define what those cells mean. The number underneath the total, the denominator, is part of the business question.

Write the metric in a sentence

“Average handling time” leaves several choices open. A more useful definition might be: “The arithmetic mean of recorded handling minutes for completed requests in this reporting week, including valid zeroes, with missing times reported separately.”

That is a proposed definition for this example, not a universal customer-service standard. Another business may need elapsed resolution time or time spent actively working. Decide the unit, the eligible records and the treatment of missing information before choosing a function.

Give each row a clear meaning, too. If one request occupies several rows, averaging those rows may answer a question about work entries rather than requests. AI cannot settle that distinction from a column heading alone.

Use four fictional rows you can check yourself

In this hypothetical Excel example, all four rows represent completed requests from the chosen week. Column B contains handling minutes:

• B2: 0, a valid recorded duration rounded to the nearest minute.
• B3: 8.
• B4: genuinely empty because the duration was not recorded.
• B5: 12.

The three recorded values total 20 minutes. Their arithmetic mean is 20 divided by 3, approximately 6.67 minutes when rounded to two decimal places. This is the mean of the recorded durations; it is not the known mean of all four requests, because one duration remains unknown.

Microsoft’s AVERAGE documentation states that, within a range or cell reference, empty cells and text are ignored, while zero values are included. With the cells specified above, =AVERAGE(B2:B5) therefore gives the mean of those three numerical values.

A different condition changes the question

Suppose an assistant instead proposes =AVERAGEIF(B2:B5,">0"). This is a hypothetical suggestion, not a measured claim about how often AI makes this mistake. Microsoft documents AVERAGEIF as averaging cells that meet a condition.

Here, the condition selects 8 and 12. Their mean is 10 minutes. That is the average of positive recorded durations, which differs from the definition that includes valid zeroes. A plausible-looking result can conceal that change.

Replacing the missing duration with zero would create a third result: 20 divided by 4, or 5 minutes. That replacement asserts something about the unrecorded request. Unless you have evidence for it, preserve the missing value rather than silently converting uncertainty into a duration.

Show how many records contributed

A mean becomes easier to assess when its record count is visible. In this example, a useful label is “6.67 minutes across three recorded durations; one duration missing”. It keeps the missing case beside the result.

Microsoft’s COUNT documentation explains that a referenced range contributes only its numerical values. =COUNT(B2:B5) returns 3 for this example, including the valid zero. It counts numerical entries, not verified requests: the worksheet structure and eligibility rules still need checking.

If a value has been stored as text, inspect the underlying record before converting it. Do not make every non-numerical entry disappear merely to obtain a clean-looking report. An exclusion should have a reason that survives a later review.

Give AI the definition and the expected answer

Here is an example request you can adapt. It asks for help implementing a decision you have already made:

“I am using Excel. B2:B5 contains handling minutes for four eligible completed requests: numerical 0, numerical 8, an empty cell, and numerical 12. Zero is valid; empty means missing, not zero. I need the mean of recorded numerical durations, a separate count of those durations and a warning about missing data. The expected mean is 20/3 and the expected numerical count is 3. Propose formulas, explain what each includes and excludes, and identify assumptions I still need to decide. Explain how the report should handle a range with no recorded numbers.”

Use fictional rows when developing the logic. Then check the proposed formula against those rows in your own application. An explanation supplied by the same assistant is useful material to examine; it is not an independent check that the formula matches your definition.

Try changes that could expose a wrong assumption

Before extending the calculation, change one thing at a time and predict the result:

• Replace the missing duration with a verified numerical 4. The mean should become 24/4, or 6 minutes.
• Restore the empty cell and remove the valid zero from the recorded data. The remaining numerical mean should be 10 minutes, with only two numerical durations.
• Try a range with no recorded numbers. Decide how to display “no recorded data” without presenting it as zero handling time.
• Add a request outside the reporting week and check that the full reporting method excludes it. The simple range formulas above do not perform that date filtering.

These are suggested checks for the stated metric, not a complete spreadsheet audit. Keep the small example and expected outcomes alongside the working sheet so a later formula change can be compared with the same requirements.

Allow time for that verification when assessing whether AI helps. The MaryChuks guide to counting work after the first AI answer explains why checking and corrections belong in the task’s cost.

A useful formula implements a defined question. Write that question clearly enough that another person can identify the records counted, the records excluded and the information still missing. Then ask AI to help express it in spreadsheet syntax.

Sources

• Microsoft Support: AVERAGE function.
• Microsoft Support: AVERAGEIF function.
• Microsoft Support: COUNT function.

Product documentation checked on 23 October 2026. All request records and calculations in the worked example are illustrative.