Skip to main content

Formulas

Put an = formula in a cell value; it shows the evaluated result (the raw =... stays editable). A1 references map to the visible columns (A = first), rows are 1-based over the displayed rows. $A$1 / $A1 / A$1 absolute markers parse and resolve to the same cell (refs never shift on fill/copy).

'total': BCellValue(value: '=SUM(A1:C1)'),   // also =A2+B2, =MAX(A3:C3)*2, etc.
'grade': BCellValue(value: '=IF(A1>=50, "pass", "fail")'),
'count': BCellValue(value: '=COUNTIF(B1:B9, ">10")'),

LET binds names for one calculation; A1# is the whole spill range of an array formula anchored at A1; scalar/array broadcasting works at the operators:

'=LET(x, A1*2, y, x+1, x+y)'       // bind x then y (y sees x), return x+y
'=SUM(A1#)'                        // sum the spill range whose anchor is A1
'=SEQUENCE(3)+1'                   // {2;3;4} — scalar broadcasts over the array

Named ranges and column (structured) references:

onLoaded: (e) => e.stateManager
  ..defineName('SALES', 'B1:B4')                 // then: =SUM(SALES)*RATE
  ..enableStructuredRefs();                      // then: =SUM([Sales]) by column title

// Register a table to unlock [#Headers] / [#Totals] item specifiers:
onLoaded: (e) => e.stateManager.defineTable(
  totals: {'sales': BCellPivotAgg.sum},
);                                               // =SUM([#Totals]), [#Headers]

Function coverage​

Arithmetic + - * / ( ), comparisons, & concat, string literals, A1/A1:B2 ranges, and:

  • Aggregate: SUM AVERAGE MIN MAX MEDIAN COUNT COUNTA COUNTBLANK PRODUCT
  • Conditional aggregate: SUMIF COUNTIF AVERAGEIF SUMIFS COUNTIFS AVERAGEIFS
  • Math/Trig: ROUND ROUNDUP ROUNDDOWN TRUNC CEILING FLOOR ABS INT SIGN MOD POWER SQRT PI SIN COS TAN EXP LN LOG10 LOG GCD LCM SUMPRODUCT
  • Logical: IF IFS IFERROR IFNA AND OR NOT XOR SWITCH
  • Text: UPPER LOWER LEFT RIGHT MID LEN TRIM CONCAT CONCATENATE SUBSTITUTE REPT EXACT FIND SEARCH REPLACE VALUE PROPER TEXT TEXTJOIN CHOOSE
  • Lookup: VLOOKUP HLOOKUP XLOOKUP INDEX MATCH XMATCH
  • Dynamic arrays (spill): FILTER SORT UNIQUE SEQUENCE SORTBY TRANSPOSE TAKE DROP VSTACK HSTACK TOCOL TOROW TEXTSPLIT
  • Date & Time: TODAY NOW DATE YEAR MONTH DAY HOUR MINUTE SECOND WEEKDAY DATEDIF EDATE EOMONTH
  • Statistical: VAR STDEV VARP STDEVP MODE LARGE SMALL PERCENTILE QUARTILE RANK
  • Financial: PMT FV PV NPV IRR
  • Information: ISBLANK ISNUMBER ISTEXT ISERROR ISNA NA N TYPE
  • Binding: LET
  • Lambdas: LAMBDA (callable value; =LAMBDA(x,x+1)(5), =LET(f, LAMBDA(...), f(...))), BYROW, MAP

Bad formulas surface #DIV/0! / #NAME? / #CIRC! / #NUM! / #N/A / #REF! / #VALUE! / #CALC!; a blocked spill shows #SPILL! at its anchor. See the widgetbook Formulas folder for live examples.

Goal Seek (What-If)​

Find the input that makes a formula cell hit a target number — Excel's Data → What-If Analysis → Goal Seek:

final found = sm.goalSeek(
  targetCell: totalCell,        // holds '=A1*B1'
  targetValue: 42,
  byChanging: qtyCell,
  lo: -1e9, hi: 1e9,            // bisection bracket
);

The found value lands as one recorded edit (a single undo restores the pre-seek state). Returns null — and restores the original — when no solution is bracketed, the target isn't numeric, or the cell rejects writes (read-only / validator).

Solver & Scenario Manager​

Solver is a multi-cell Goal Seek (coordinate descent); Scenario Manager saves and restores named sets of input-cell values.

// Drive several inputs until the target hits a value:
sm.solve(targetCell: total, targetValue: 30, byChanging: [a, b], lo: 0, hi: 6);

// Named input-cell value sets:
sm.saveScenario('Best case', [a, b]);
sm.applyScenario('Best case');   // sm.scenarios; sm.removeScenario('Best case');

Solver restores all inputs on failure. Unconstrained / target-value only (no constraints or min/max objective).