SPB Git

Repository Public

EVALUATION-run1-guided-json.md 14.0 KB 129 lines markdown

1# SheetIntelligence — measured evaluation23Generated by `swift test` (`RealModelTests.fullEvaluation`) against the real on-device model. Nothing below is scripted or edited by hand.45- Date: 2026-11-21T07:27:42Z6- macOS: Version 27.0 (Build 26A5425a)7- Model availability: available(modelDescription: "Apple on-device model (AFM 3 Core Advanced)")8- Function registry: 465 functions (core + FunctionsNumeric + FunctionsText + FunctionsLookup)9- Workbook: synthetic sales table `Sales!A1:F21` (Region, Product, Units, Unit Price, Revenue, Date), sheet `Params` with the name `TaxRate`; active cell `H2`10- Decoding: greedy (temperature 0), guided generation1112## Formula suggestions (20 fixed requests)1314| Measure | Result |15|---|---|16| Requests answered without error | 20 / 20 |17| Suggestions produced | 57 |18| Suggestions that passed validation (parse, functions, references, engine evaluation) | 38 / 57 |19| Requests with at least one valid suggestion on the first pass | 15 / 20 |20| Requests with at least one valid suggestion (after the single repair round) | 17 / 20 |21| Requests whose first listed suggestion is valid and computes the expected value | 12 / 20 |22| Requests where some valid suggestion computes the expected value | 13 / 20 |23| Repair rounds triggered / repairs that produced the expected value | 5 / 1 |24| Mean time per request | 10.8 s |2526Expected values are computed independently in Swift from the generator of the synthetic data. A suggestion that validates but computes another value counts as wrong.2728| # | Request | Expected | Correct | Suggestions (raw model text — verdict) |29|---|---|---|---|---|30| 1 | Total revenue | 4667.5 | no | `=SUMIFS(E2:E21, C2:C21, ">=0", C2:C21, "<=1000000"), ` — invalid: It is not a valid formula: Unexpected ','.<br>`=SUMIFS(E2:E21, C2:C21, ` (repair) — invalid: It is not a valid formula: Unexpected end of formula. |31| 2 | Average number of units sold | 16.3 | yes | `=AVERAGE(C2:C21)` — valid, correct (16.3)<br>`=AVERAGEIF(A2:A21, "North", C2:C21)` — valid, WRONG value (15)<br>`=AVERAGEIFS(C2:C21, A2:A21, "South")` — valid, WRONG value (17.4) |32| 3 | Highest unit price | 22.25 | yes | `=MAX(D2:D21)` (repair) — valid, correct (22.25)<br>`=MAXIFS(D2:D21, A2:A21, "North"), "explanation": ` — invalid: It is not a valid formula: Unexpected ','. |33| 4 | Lowest revenue | 47.5 | yes | `=MIN(E2:E21)` — valid, correct (47.5)<br>`=MINIFS(E2:E21, C2:C21, 0)` — valid, WRONG value (0)<br>`=MINIFS(E2:E21, A2:A21, "North")` — valid, correct (47.5) |34| 5 | How many sales are in the North region? | 5 | yes | `=AVERAGEIF(A2:A21, "North", C2:C21)` — valid, WRONG value (15)<br>`=COUNTIF(A2:A21, "North")` — valid, correct (5)<br>`=SUMIF(A2:A21, "North", C2:C21)` — valid, WRONG value (75) |35| 6 | Total revenue for the West region | 1141.25 | yes | `=SUMIFS(E2:E21, A2:A21, "West")` — valid, correct (1141.25)<br>`=SUMIF(A2:A21, "West", E2:E21)` — valid, correct (1141.25)<br>`=SUMIFS(E2:E21, A2:A21, "West", C2:C21, 0)` — valid, WRONG value (0) |36| 7 | Number of sales with more than 20 units | 6 | no | `=SUMIFS(C2:C21, D2:D21, ` — invalid: It is not a valid formula: Unexpected end of formula.<br>`=SUMIFS(C2:C21, D2:D21, >20)` (repair) — invalid: It is not a valid formula: Unexpected '>'. |37| 8 | Total units sold of the product Gadget | 111 | yes | `=SUMIFS(C2:C21, B2:B21, "Gadget")` — valid, correct (111)<br>`=AVERAGEIF(B2:B21, "Gadget", C2:C21)` — valid, WRONG value (15.8571428571429)<br>`=SUMPRODUCT(--(B2:B21="Gadget")*(C2:C21))` — valid, correct (111) |38| 9 | Average revenue of Widget sales | 177.785714285714 | yes | `=AVERAGEIF(B2:B21, "Widget", E2:E21)` — valid, correct (177.785714285714)<br>`=AVERAGEIFS(E2:E21, B2:B21, "Widget')` — invalid: It is not a valid formula: Unterminated string.<br>`=DAVERAGE(B2:B21, "Revenue", "Widget')` — invalid: It is not a valid formula: Unterminated string. |39| 10 | Revenue of the first sale with tax added, using the TaxRate name | 54.625 | no | `=SUMIFS(E2:E21, C2:C21, 2, D2:D21, 0, TaxRate)` — valid, WRONG value (#VALUE!)<br>`=SUMIFS(E2:E21, C2:C21, 3, D2:D21, 0, TaxRate)` — valid, WRONG value (#VALUE!)<br>`=SUMIFS(E2:E21, C2:C21, 2, C2:C21, 12, D2:D21, 0, TaxRate)` — valid, WRONG value (#VALUE!) |40| 11 | Number of different products | 3 | no | `=COUNTIFS(B2:B21, "*" & TRIM(A2:A21))` — valid, WRONG value (array 20×1 starting with 0)<br>`=COUNTIF(B2:B21, UNIQUE(B2:B21))` — valid, WRONG value (array 3×1 starting with 7)<br>`=SUMPRODUCT(--TEXTJOIN(",", TRUE, B2:B21))` — valid, WRONG value (#VALUE!) |41| 12 | Region of the sale with the highest revenue | "East" | no | `=INDEX(A2:A21, AGGREGATE(1, 1, COUNTIFS(E2:E21, E2:E21), 0))` (repair) — valid, WRONG value (array 20×1 starting with "North")<br>`=INDEX(A2:A21, AGGREGATE(103, 2, COUNTIFS(E2:E21, ` — invalid: It is not a valid formula: Unexpected end of formula.<br>`=INDEX(A2:A21, AGGREGATE(103, 2, COUNTIFS(E2:E21, ` — invalid: It is not a valid formula: Unexpected end of formula.<br>`=INDEX(A2:A21, AGGREGATE(103, 2, COUNTIFS(E2:E21, ` — invalid: It is not a valid formula: Unexpected end of formula. |42| 13 | Total revenue for Widget in the North region | 237.5 | yes | `=SUMIFS(E2:E21, A2:A21, "North", B2:B21, "Widget")` — valid, correct (237.5)<br>`=SUMIFS(E2:E21, A2:A21, "North"` — invalid: It is not a valid formula: Missing closing parenthesis.<br>`=SUMIFS(E2:E21, B2:B21, "Widget"` — invalid: It is not a valid formula: Missing closing parenthesis. |43| 14 | Median unit price | 14 | yes | `=MEDIAN(D2:D21)` — valid, correct (14)<br>`=MEDIAN(COUNTIF(D2:D21, D2:D21))` — valid, WRONG value (7)<br>`=MEDIAN(IF(A2:A21=` — invalid: It is not a valid formula: Unexpected end of formula. |44| 15 | Share of total revenue that comes from the East region | 0.24242099625067 | no | `=AVERAGEIF(A2:A21, "East", E2:E21)` — valid, WRONG value (226.3)<br>`=CHOOSECOLS(CHOOSECOLS(E2:E21, 1), A2:A21)` — valid, WRONG value (#VALUE!)<br>`=AVERAGEIFS(E2:E21, A2:A21, "East')` — invalid: It is not a valid formula: Unterminated string. |45| 16 | Revenue of the first sale rounded to the nearest whole number | 48 | yes | `=CEILING.MATH(E2, 1)` — valid, correct (48)<br>`=FLOOR.MATH(E2, 1)` — valid, WRONG value (47)<br>`=EVEN(E2)` — valid, correct (48) |46| 17 | Largest number of units in a single sale in the South region | 27 | yes | `=CEILING.MATH(MAX(C2:C21), 1)` — valid, correct (27)<br>`=MAXIFS(C2:C21, A2:A21, "South")` — valid, correct (27)<br>`=COUNTIFS(A2:A21, "South", C2:C21, ">=` — invalid: It is not a valid formula: Unterminated string. |47| 18 | Second largest revenue | 378.25 | yes | `=LARGE(E2:E21, 2)` — valid, correct (378.25)<br>`=MAXIFS(E2:E21, E2:E21, E2:E21 - LARGE(E2:E21, 1))` — valid, WRONG value (array 20×1 starting with 0)<br>`=MAX(E2:E21, E2:E21 - LARGE(E2:E21, 1))` — valid, WRONG value (422.75) |48| 19 | Number of sales dated in February 2026 | 7 | no | `=COUNTIFS(F2:F21, ` — invalid: It is not a valid formula: Unexpected end of formula.<br>`=COUNTIFS(F2:F21, ">=01/02/2026", F2:F21, "<=31/02/2026"), ` (repair) — invalid: It is not a valid formula: Unexpected ','. |49| 20 | The text High if total units are above 300, otherwise Low | "High" | yes | `=IF(COUNTBLANK(C2:C21)>0,"Low","High")` — valid, correct ("High")<br>`=IF(COUNTIF(C2:C21,>=300),"High","Low")` — invalid: It is not a valid formula: Unexpected '>'.<br>`=IF(SUMIF(C2:C21,>=300),"High","Low")` — invalid: It is not a valid formula: Unexpected '>'. |5051## Other tasks (one observation each)5253### Explain a formula (English)5455Formula `=SUMIF(A2:A21,"North",E2:E21)/SUM(E2:E21)`, engine result 0.227370112479914.5657> The formula in H2 calculates the ratio of a filtered sum to the total revenue. It takes the sum of widget revenue where the region is "North" from E2:E21, using A2:A21 as the region list. The filtered sum is 1061.25, derived from the values in E2:E21 for "North". The total revenue in E2:E21 is 4667.5. Dividing these gives the result 0.227370112479914. The current result comes from the engine’s exact calculation steps.5859Numbers not found among the engine's values: none.6061### Explain a formula (app locale fr-CA)6263`supportsLocale(fr_CA)` → answer language appLanguage(code: "fr"). Answer looks French (stop-word heuristic): true.6465> La formule dans la cellule H2 de la feuille « Sales » calcule la somme des valeurs dans E2:E21 où la colonne A contient « North », divisée par la somme totale des valeurs dans E2:E21. Les cellules A2:A21 représentent les régions et E2:E21 représentent les revenus pour chaque article. L’étape SUMIF renvoie 1061.25, car seules les lignes marquées « North » comptent. La somme totale de E2:E21 est 4667.5. Le résultat 0.227370112479914 provient de la division de 1061.25 par 4667.5. Ce résultat correspond directement aux étapes de l’évaluation.6667### Describe data6869> This dataset in sheet "Sales" contains 20 data rows across 6 columns, with complete values in all columns—0 missing. Column A ("Region") holds 20 text values, 4 distinct regions, with "North", "South", "East", and others, where "North", "South", and "East" appear 5 times each—most frequent. Column B ("Product") has 20 text values, 3 distinct products, with "Widget", "Gadget", and "Gizmo", where "Widget" and "Gadget" appear 7 times each, and "Gizmo" 6 times—most frequent. Column C ("Units") shows 20 integers, 20 distinct values, ranging from 5 to 27, with sum 326—most frequent values not specified. Column D ("Unit Price") contains 20 numbers, 3 distinct prices, min 9.5, max 22.25, with sum 298—most frequent values not specified. Column E ("Revenue") has 20 numbers, 20 distinct values, min 47.5, max 422.75, with sum 4667.5—most frequent values not specified. Column F ("Date") lists 20 dates from 2026-01-01 to 2026-03-25, all complete. The ranges show tight min/max bounds for Units (5–27) and Prices (9.5–22.25), but Revenue has a wide range (47.5–422.75). The number of distinct values is high in Units and Revenue, while Regions and Products have fewer distinct entries.7071Numbers verified against the profile: 32. Flagged as unverified: none.7273### Suggest charts7475- bar: x = Region, y = Units — Bar chart shows Units per Region clearly, with Region as categorical x-axis and Units as numeric y-values.76- column: x = Product, y = Units — Column chart displays Units across Product categories, with Product as x and Units as y-values showing frequency.77- stackedColumn: x = Product, y = Units, Revenue — Stacked column chart layers Units and Revenue by Product, showing both metrics per Product on the x-axis.78- line: x = Date, y = Revenue — Line chart tracks Revenue across Dates, with Date on x and Revenue on y to show trends over time.79804 accepted after validation.8182### Transformation plan (goal: "total revenue per region, highest first")83841. filter [Region, Date]: Keep only rows where Date is between 2026-01-01 and 2026-03-25852. groupBy [Region, Revenue] → [RevenuePerRegion]: Group Revenue by Region and compute total Revenue per Region863. sort [RevenuePerRegion]: Sort RevenuePerRegion in descending order (highest first)87883 of 3 steps consistent with the profile.8990### Review auditor findings (4 findings from the deterministic auditor)9192> The sheet contains several issues, with the most critical being a division error and a formula inconsistency that may indicate a copied entry.93941. `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).952. `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…).963. `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.974. `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.9899Invented ids rejected: 0.100101### Explain statistics (descriptive summary of Units)102103> The result shows summary data from a sample of 20 observations. The mean is 15.25, meaning the average value is slightly above 15. The median is 15, which is the middle value when the numbers are sorted. The standard deviation is 6.71, indicating how spread out the values are from the average. The minimum value is 5 and the maximum is 27. These numbers help understand the center and spread of the data. The sample size is 20, so the results reflect only that group. The data may not represent the entire population, so conclusions should be made cautiously.104105Numbers not in the table: none.106107### Python help (request: "total revenue per region written next to the table")108109```python110import orix111112# Read data from H2:H3, excluding header113rng = sheet.range("H2:H3")114df = rng.to_dataframe(header=False)115116# Compute revenue per region117revenue_per_region = df.iloc[:,0] * df.iloc[:,1].replace("#DIV/0!", 0)118119# Write result starting at H1120sheet.range("H1").write(revenue_per_region)121```122123Syntax check (compile only, never executed): passed(interpreter: "/opt/homebrew/bin/python3"). Unknown `orix` calls: none.124125### Document a sheet (model paragraph only; the rest of the document is deterministic)126127> This sheet is most likely for calculating a weighted average revenue for two product types, "Widget" and "Gadget", based on regional filters and unit pricing. The inputs—20 values for widget units, 20 values for unit prices, and a regional label column—feed the formulas that compute individual revenue and then aggregate them. The output in H2 uses a sumif to divide total northern widget revenue by total widget revenue, while H3 divides individual widget revenue by a difference in widget count. The structure suggests the sheet aggregates data from multiple rows into final figures.128129
Files main
Menu

Theme