SPB Git

Repository Public

EVALUATION.md 14.3 KB

# SheetIntelligence — measured evaluation

Generated by swift test (RealModelTests.fullEvaluation) against the real on-device model. Nothing below is scripted or edited by hand, except the note that follows and the "Decoding" line, corrected on 2026-11-21 without re-running the model (no measured value was changed).

How to regenerate (since 2026-11-21). The evaluation is opt-in: ORIX_AI_EVAL=1 swift test in Packages/SheetIntelligence. A plain swift test (variable unset, or any value other than 1, 0 included) skips every test that calls the on-device model and never rewrites this file.

  • Date: 2026-11-21T07:36:55Z
  • macOS: Version 27.0 (Build 26A5425a)
  • Model availability: available(modelDescription: "Apple on-device model (AFM 3 Core Advanced)")
  • Function registry: 465 functions (core + FunctionsNumeric + FunctionsText + FunctionsLookup)
  • Workbook: synthetic sales table Sales!A1:F21 (Region, Product, Units, Unit Price, Revenue, Date), sheet Params with the name TaxRate; active cell H2
  • Decoding: greedy (temperature 0) for the formula suggestions, Python help, review, charts and transformation plan; formulas and Python as plain text lines, review / charts / plan with guided generation. In this recorded run the four streamed prose tasks (explain a formula, explain statistics, describe data, sheet purpose) passed no temperature and used the system's default sampling; since 2026-11-21 they request greedy decoding like the others (Intelligence.stream), and the observations below were not re-run
  • An earlier run of the same 20 requests with formulas generated inside guided JSON is kept verbatim in EVALUATION-run1-guided-json.md (13 / 20 correct); see "Changes between runs" at the end

# Formula suggestions (20 fixed requests)

Measure Result
Requests answered without error 20 / 20
Suggestions produced 35
Suggestions that passed validation (parse, functions, references, engine evaluation) 33 / 35
Requests with at least one valid suggestion on the first pass 20 / 20
Requests with at least one valid suggestion (after the single repair round) 20 / 20
Requests whose first listed suggestion is valid and computes the expected value 11 / 20
Requests where some valid suggestion computes the expected value 12 / 20
Repair rounds triggered / repairs that produced the expected value 0 / 0
Mean time per request 28.2 s

Expected values are computed independently in Swift from the generator of the synthetic data. A suggestion that validates but computes another value counts as wrong.

# Request Expected Correct Suggestions (raw model text — verdict)
1 Total revenue 4667.5 no =SUMIFS(E2:E21, C2:C21, ">0", F2:F21, "<="&DATE(2023,12,31)) — valid, WRONG value (0)
=SUMIFS(E2:E21, C2:C21, "<>", TaxRate) — valid, WRONG value (#VALUE!)
2 Average number of units sold 16.3 no =AVERAGEIF(C2:C21, "", D2:D21) — valid, WRONG value (#DIV/0!)
=AVERAGEIF(C2:C21, "", SUMIF(C2:C21, "", D2:D21)) — valid, WRONG value (#VALUE!)
3 Highest unit price 22.25 yes =MAXIFS(D2:D21, A2:A21, "North") — valid, correct (22.25)
=MAXIFS(D2:D21, F2:F21, "2023-01-01") — valid, WRONG value (0)
4 Lowest revenue 47.5 yes =MIN(E2:E21) — valid, correct (47.5)
=MINIFS(E2:E21, F2:F21, "2023-01-01", F2:F21, "2023-01-02") — valid, WRONG value (0)
5 How many sales are in the North region? 5 yes =COUNTIF(A2:A21, "North") — valid, correct (5)
6 Total revenue for the West region 1141.25 yes =SUMIFS(E2:E21, A2:A21, "West") — valid, correct (1141.25)
=SUMIFS(E2:E21, A2:A21, "West", TaxRate) — valid, WRONG value (#VALUE!)
7 Number of sales with more than 20 units 6 yes =COUNTIF(C2:C21, ">20") — valid, correct (6)
8 Total units sold of the product Gadget 111 yes =SUMIFS(C2:C21, B2:B21, "Gadget") — valid, correct (111)
9 Average revenue of Widget sales 177.785714285714 yes =AVERAGEIF(B2:B21, "Widget", E2:E21) — valid, correct (177.785714285714)
=AVERAGEIF(B2:B21, "Widget", C2:C21) — valid, WRONG value (18.7142857142857)
10 Revenue of the first sale with tax added, using the TaxRate name 54.625 yes =E2* (1 + TaxRate) — valid, correct (54.625)
=E2* (1 + TAXRATE) — valid, correct (54.625)
11 Number of different products 3 no =COUNTIF(B2:B21, "<>"&TEXT(DATE(2023,1,1),"00000")) — valid, WRONG value (20)
=COUNTIFS(B2:B21, "<>"&TEXT(DATE(2023,1,1),"00000")) — valid, WRONG value (20)
12 Region of the sale with the highest revenue "East" yes =INDEX(A2:A21, MATCH(MAX(E2:E21), E2:E21, 0)) — valid, correct ("East")
=INDEX(A2:A21, MATCH(MAX(E2:E21), E2:E21, 0), 0) — valid, correct ("East")
13 Total revenue for Widget in the North region 237.5 yes =SUMIFS(E2:E21, A2:A21, "Widget", C2:C21, "North") — valid, WRONG value (0)
=SUMIFS(E2:E21, A2:A21, "North", B2:B21, "Widget") — valid, correct (237.5)
14 Median unit price 14 yes =MEDIAN(D2:D21) — valid, correct (14)
=MEDIAN(INDEX(D2:D21, 0)) — valid, correct (14)
15 Share of total revenue that comes from the East region 0.24242099625067 no =SUMIFS(E2:E21, A2:A21, "East", D2:D21, 0) — valid, WRONG value (0)
=SUMIFS(E2:E21, A2:A21, "East", C2:C21, 0) — valid, WRONG value (0)
16 Revenue of the first sale rounded to the nearest whole number 48 no =ROUND(E2*TaxRate, 0) — valid, WRONG value (7)
17 Largest number of units in a single sale in the South region 27 yes =MAX(IF(A2:A21="South", C2:C21)) — valid, correct (27)
=MAXIF(A2:A21, "South", C2:C21) — invalid: The function MAXIF does not exist in ORIX Sheet.
18 Second largest revenue 378.25 no =MAXIFS(E2:E21, F2:F21, ">=46023", F2:F21, "<=46026") — valid, WRONG value (168)
=INDEX(E2:E21, MATCH(2, COUNTIFS(E2:E21, ">="&46023, E2:E21, "<="&46026), 0)) — valid, WRONG value (#N/A)
19 Number of sales dated in February 2026 7 no =COUNTIFS(F2:F21, "February 2026") — valid, WRONG value (0)
20 The text High if total units are above 300, otherwise Low "High" no =IF(C2:C21>300, "High", "Low") — valid, WRONG value (array 20×1 starting with "Low")
=COUNTIF(C2:C21, ">300")>0 ? "High" : "Low" — invalid: It is not a valid formula: Unexpected '?'.

# Other tasks (one observation each)

# Explain a formula (English)

Formula =SUMIF(A2:A21,"North",E2:E21)/SUM(E2:E21), engine result 0.227370112479914.

The formula in H2 calculates the ratio of SUMIF results to the total revenue. SUMIF(A2:A21, "North", E2:E21) adds revenues where the region is "North", returning 1061.25. SUM(E2:E21) adds all revenues, returning 4667.5. The formula divides 1061.25 by 4667.5, producing 0.227370112479914. This result reflects the share of "North" region revenue within total revenue. The engine followed the steps exactly as described, using the given values.

Numbers not found among the engine's values: none.

# Explain a formula (app locale fr-CA)

supportsLocale(fr_CA) → answer language appLanguage(code: "fr"). Answer looks French (stop-word heuristic): true.

La formule dans la cellule H2 de la feuille « Sales » calcule la somme des valeurs de Revenu dans la colonne E où la région correspond à « North », divisée par la somme totale des valeurs de Revenu dans la colonne E. Les cellules A2:A21 représentent les régions et les cellules E2:E21 représentent les revenus. L’étape 4 de l’évaluation donne 1061.25 pour la somme des revenus de la région « North ». L’étape 5 donne 4667.5 pour la somme totale des revenus. La division de 1061.25 par 4667.5 donne le résultat actuel de 0.227370112479914. Ce résultat correspond à la proportion des revenus de la région « North » par rapport au total des revenus.

# Describe data

The dataset in sheet "Sales" (A1:F21) contains 20 data rows across 6 columns. Column A ("Region") holds 20 text values, all present (0 missing), with 4 distinct regions and the most frequent being "North" (5), "South" (5), and "East" (5). Column B ("Product") includes 20 text values, all present (0 missing), with 3 distinct products and the most frequent being "Widget" (7), "Gadget" (7), and "Gizmo" (6). Column C ("Units") has 20 integer values, all present (0 missing), with 20 distinct values, ranging from min 5 to max 27, and the sum is 326. Column D ("Unit Price") shows 20 number values, all present (0 missing), with 3 distinct prices, min 9.5 to max 22.25, and the sum is 298. Column E ("Revenue") contains 20 number values, all present (0 missing), with 20 distinct values, min 47.5 to max 422.75, and the sum is 4667.5. Column F ("Date") lists 20 date values, all present (0 missing), all distinct, spanning from 2026-01-01 to 2026-03-25. The data is fully complete across all columns, with no missing entries. The ranges in "Units" and "Revenue" are very wide, and the frequent values in "Product" show balanced repeats. The date range is tight and consistent.

Numbers verified against the profile: 34. Flagged as unverified: none.

# Suggest charts

  • bar: x = Region, y = Units — Bar chart shows Units per Region clearly, with Region as categorical x-axis and Units as numeric y-values.
  • column: x = Product, y = Units — Column chart displays Units across Product categories, with Product as x and Units as y-values showing frequency.
  • stackedColumn: x = Product, y = Units, Revenue — Stacked column chart layers Units and Revenue by Product, showing both metrics per Product on the x-axis.
  • line: x = Date, y = Revenue — Line chart tracks Revenue across Dates, with Date on x and Revenue on y to show trends over time.

4 accepted after validation.

# Transformation plan (goal: "total revenue per region, highest first")

  1. filter [Region, Date]: Keep only rows where Date is between 2026-01-01 and 2026-03-25
  2. groupBy [Region, Revenue] → [RevenuePerRegion]: Group Revenue by Region and compute total Revenue per Region
  3. sort [RevenuePerRegion]: Sort RevenuePerRegion in descending order (highest first)

3 of 3 steps consistent with the profile.

# Review auditor findings (4 findings from the deterministic auditor)

The sheet contains several issues, with the most critical being a division error and a formula inconsistency that may indicate a copied entry.

  1. errorValue:Sales!H3 — The formula =E2/(C2-C2) results in #DIV/0! because the denominator (C2-C2) equals 0, and C2 contains 5. Fix: Fill in or correct the denominator, or guard the division: =IF(denominator=0, '', numerator/denominator).
  2. inconsistentFormula:Sales!E9 — E9 uses =C9*14 while neighbours follow a pattern, indicating a possible edit or copy error. Fix: Check whether the difference is intended. If not, copy the neighbouring formula into E9; if it is, make the exception visible (label or not…).
  3. hardcodedConstant:Sales!E9 — The formula =C9*14 contains the number 14 directly, which is an assumption not visible or changeable. Fix: Move the value to a labelled input cell (or a defined name) and refer to it, unless it is a true constant of the calculation.
  4. unusedName:TaxRate — The name TaxRate (=Params!$B$1) is not used by any formula, validation or conditional format. Fix: Delete the name, or use it in the formulas that read those cells.

Invented ids rejected: 0.

# Explain statistics (descriptive summary of Units)

This result shows summary data from a group of 20 observations, where "Count" means the number of items measured—20. The "Mean" is 15.25, which is the average value, and the "Median" is 15, meaning the middle value when sorted. The "Std. deviation (sample)" is 6.71, indicating how spread out the numbers are from the average, and the "Min" is 5, the lowest value, while the "Max" is 27, the highest. These figures help understand the center and spread of the data. The main caveat is that this is only descriptive—no conclusions about cause or effect can be drawn. The data includes only these four key figures without context on the original test.

Numbers not in the table: none.

# Python help (request: "total revenue per region written next to the table")

python
import orix
import pandas as pd

# Read data from H2 to H3, no header
data_df = sheet.range("H2:H3").to_dataframe()

# Compute total revenue per region
total_revenue = data_df.iloc[0] * data_df.iloc[1]

# Write result starting at H1
sheet.range("H1").write(total_revenue)

Syntax check (compile only, never executed): passed(interpreter: "/opt/homebrew/bin/python3"). Unknown orix calls: none.

# Document a sheet (model paragraph only; the rest of the document is deterministic)

This sheet is most likely for calculating a weighted total based on region and item type, using revenue data and a tax rate. The inputs—units, unit price, and a tax rate from "Params"—are used in formulas to generate revenue per item. The outputs—Widget and Gadget totals—come from summing revenue for "North" and dividing by total revenue, with a modified revenue calculation for Gadget. The structure suggests a comparison of two items under specific conditions.

# Changes between runs

Run 1 (EVALUATION-run1-guided-json.md, same machine, same day, same 20 requests): 38 / 57 suggestions valid, 13 / 20 requests correct. Its failures showed three general defects, which were changed before this run (the 20 requests and their expected values were not changed, and no request-specific wording was added to the prompts):

  1. Formulas were generated inside JSON strings (guided generation). Formulas contain double quotes, and the answers were cut at the first quote (=COUNTIFS(F2:F21, ) or used a single quote instead. Formulas (and Python) are now requested as plain FORMULA: / WHY: lines and parsed strictly.
  2. The prompt listed the signatures of the functions "closest to the request" (keyword match), which pushed the model towards exotic functions (AGGREGATE, CHOOSECOLS, EVEN). It now lists function names only and asks for the simplest formula; it also states two general syntax facts (criteria are quoted text such as ">20"; dates are written with DATE).
  3. The model was asked for "up to 3 different formulas", which produced valid formulas that answer a different question. It is now asked for the best formula, with alternatives only when they answer the same request.

No further prompt changes were made after this run.

Files main
Menu

Theme