How to test AI models for Excel formulas: 5 practical cases

There is no permanently “best” AI model for Excel formulas. Model versions, free-tier access, interfaces, and tool features change, while the correct formula depends on the workbook, Excel version, locale, and edge cases included in the request.

A useful comparison therefore needs a repeatable test. The five cases below cover conditional totals, classification, multi-criteria lookup, text extraction, and a running monthly total. Each section includes a reference solution and checks that a model should mention.

How to compare the models fairly

1. Record the exact model name, version or date, product tier, and whether web or code tools are enabled.
2. Start a new conversation for each model and use the same prompt.
3. Specify the Excel version, regional separator, data rows, and expected behavior for blanks and errors.
4. Paste each formula into the same copied workbook.
5. Test normal, boundary, blank, invalid, duplicate, and not-found cases.
6. Score the first answer separately from any corrected answer after feedback.
7. Repeat important prompts because model output can vary between runs.

Do not rank a model from whether its formula merely looks familiar. A syntactically valid formula can return a plausible but incorrect total.

A simple scoring rubric

Criterion	Question
Correctness	Does the formula return the expected result for every test row?
Requirement coverage	Did it implement every stated condition and boundary?
Version compatibility	Does the formula work in the specified Excel version?
Error handling	Are blanks, invalid values, and no-match cases handled as requested?
Maintainability	Are ranges bounded, references intentional, and the explanation understandable?
Restraint	Did the model avoid unnecessary alternatives and unsupported assumptions?

Test 1: SUMIFS with multiple conditions

Prompt:

I use Excel [VERSION]. Rows 2:1000 contain sales data:
A = salesperson, B = region, C = product category, D = sales amount.
Write a formula for F2 that totals numeric sales in the North region for the Electronics category. Explain every range and use bounded ranges.

Reference formula:

=SUMIFS($D$2:$D$1000,$B$2:$B$1000,"North",$C$2:$C$1000,"Electronics")

All criteria ranges and the sum range must have identical dimensions. Excel text comparisons in this context are normally not case-sensitive. Test blanks, text inside the amount column, leading spaces in categories, and a region with no matching rows.

A stronger answer may suggest criteria cells instead of hardcoded words:

=SUMIFS($D$2:$D$1000,$B$2:$B$1000,F2,$C$2:$C$1000,G2)

That alternative is useful only if the prompt defines where those criteria cells are located.

Test 2: classify scores with boundaries

Prompt:

Column E contains scores from 0 to 100.
In F2 return:
90–100 = Distinction
75–89 = Merit
60–74 = Pass
0–59 = Fail
Return a blank when E2 is blank and "Check score" for nonnumeric values or numbers outside 0–100.
Give a formula compatible with Excel [VERSION].

Reference formula using nested IF:

=IF(E2="","",IF(NOT(ISNUMBER(E2)),"Check score",IF(OR(E2<0,E2>100),"Check score",IF(E2>=90,"Distinction",IF(E2>=75,"Merit",IF(E2>=60,"Pass","Fail"))))))

The order of the thresholds matters. Test exactly 0, 59, 60, 74, 75, 89, 90, and 100, plus a blank, text value, negative number, and 101.

If the target Excel supports IFS, an alternative may be easier to read:

=IFS(E2="","",NOT(ISNUMBER(E2)),"Check score",OR(E2<0,E2>100),"Check score",E2>=90,"Distinction",E2>=75,"Merit",E2>=60,"Pass",TRUE,"Fail")

Test 3: look up with two criteria

Prompt:

Rows 2:1000 contain a price table:
A = product, B = size, C = price.
E2 contains the required product and F2 the required size.
Return the matching price in G2 or "Not found" when no row matches.
Product names can repeat for different sizes.
Use Excel [VERSION] and explain what happens if duplicate product-size pairs exist.

Reference formula for Microsoft 365 or supported XLOOKUP versions:

=XLOOKUP(1,($A$2:$A$1000=E2)*($B$2:$B$1000=F2),$C$2:$C$1000,"Not found")

Reference INDEX/MATCH approach:

=IFERROR(INDEX($C$2:$C$1000,MATCH(1,($A$2:$A$1000=E2)*($B$2:$B$1000=F2),0)),"Not found")

Older Excel releases may require the second formula to be confirmed as an array formula. The correct entry method depends on the actual version.

A concatenation lookup such as matching product&size can create collisions unless a safe delimiter and escaping rule are used. Both reference formulas return the first matching row. If duplicate product-size pairs are invalid, the model should suggest a separate duplicate check rather than silently choosing one.

Test 4: extract text between delimiters

Prompt:

A2 contains an order code in the exact pattern PREFIX-NUMBER-SUFFIX, such as ORD-12345-UK.
Return the middle segment as text in B2.
Return "Check code" if either delimiter is missing.
Provide a Microsoft 365 formula and an older-Excel alternative.

Microsoft 365 reference formula:

=IFERROR(TEXTBEFORE(TEXTAFTER(A2,"-"),"-"),"Check code")

Older-Excel reference formula:

=IFERROR(MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1),"Check code")

The output remains text, preserving leading zeros such as 00123. Wrapping it in VALUE is appropriate only if the identifier is genuinely a number for calculation.

A TEXTSPLIT answer should explicitly return the second element. Returning the entire split array does not satisfy a single-cell middle-segment requirement.

Test 5: running total that resets each month

Prompt:

Rows are sorted in ascending date order.
Column A contains valid Excel dates and column B contains daily revenue.
In C2, create a running total through the current row that includes only dates in the same calendar month and year as A2. The total must reset when the month changes and must not combine January from different years.
Use bounded expanding ranges and Excel [VERSION].

Reference formula:

=SUMIFS($B$2:B2,$A$2:A2,">="&EOMONTH(A2,-1)+1,$A$2:A2,"<"&EOMONTH(A2,0)+1)

This formula expands from row 2 through the current row and restricts dates to the first day of A2's month through the day before the next month. It distinguishes years naturally through the date boundaries.

Test a December-to-January transition, two different years, a month with one row, blank amounts, and dates stored as text. If rows are not chronologically sorted, “running total” must be defined more carefully because row order and date order differ.

A SUMPRODUCT solution can also work when it checks both month and year:

=SUMPRODUCT((MONTH($A$2:A2)=MONTH(A2))*(YEAR($A$2:A2)=YEAR(A2))*$B$2:B2)

Omitting the year is a serious logic error in data covering more than 12 months.

Edge cases separate a demo from a useful formula

Simple requests often produce the same familiar answer across models. Differences become visible when the prompt defines:

• Blank cells and numbers stored as text
• Invalid ranges and boundary values
• Duplicate lookup keys
• No-match behavior
• Excel version and function availability
• Regional comma or semicolon separators
• Dates stored as real dates versus text
• Large datasets where whole-column array calculations may be slow
• Whether a returned identifier must preserve leading zeros

How to ask for a more reliable formula

Act as an Excel formula reviewer.
Excel version: [VERSION]
Regional list separator: [COMMA OR SEMICOLON]
Worksheet layout: [COLUMNS AND ROWS]
Desired result: [PRECISE RULES]
Blank/error behavior: [RULES]
Example rows and expected outputs: [SANITIZED EXAMPLES]

Return:
1. One primary formula
2. Explanation of each range and condition
3. Version or array-entry requirements
4. Assumptions
5. Five test cases with expected results

Do not invent worksheet names, columns, helper cells, or data not supplied.

Never paste confidential workbook data into an unapproved service. A schema and a few synthetic rows are usually enough to generate and test a formula.

Which model should you choose?

Choose the model and interface that perform best on your own workbook benchmark, explain assumptions clearly, meet your privacy requirements, and remain practical under your budget and hardware constraints. A hosted free tier may change or impose limits; a local open-weight model still requires compatible hardware and maintenance.

For production spreadsheets, the final control is not the brand of the model. It is a test set with known answers, peer review for consequential work, and a formula that someone on the team understands well enough to maintain.