packages/sheets/src/formula/functions-financial.ts (1)

1258-1780: ⚠️ Potential issue | 🟡 Minor

Financial functions should return #NUM! (not #VALUE!) for zero denominators and numeric-domain violations, consistent with Google Sheets behavior.

The PR standardizes numeric errors to #NUM!, but several financial functions still return #VALUE! for cases that should be #NUM!:

• DISC (line 1258), YIELDDISC (line 1313), RECEIVED (line 1429), INTRATE (line 1457), PRICEMAT (line 1600), YIELDMAT (line 1633/1635): Return #VALUE! when divisors are zero; should return ErrNode.NUM.
• RRI (line 1780): Returns #VALUE! when pv.v === 0 or nper.v === 0; should return ErrNode.NUM.
• PDURATION (line 1758): Returns #VALUE! for negative/zero rates and values; should return ErrNode.NUM.
• RATE (line 419), IRR (line 462/467), XIRR (line 793/798): Non-convergence paths return #VALUE!; should return ErrNode.NUM per Google Sheets convention.

Consider consolidating these into a follow-up PR for consistency.

🤖 Prompt for AI Agents

Verify each finding against the current code and only fix it if needed.

In `@packages/sheets/src/formula/functions-financial.ts` around lines 1258 - 1780,
Several financial functions return ErrNode.VALUE (i.e., `#VALUE`!) for zero
denominators or numeric-domain violations but per PR guidance and Google Sheets
behavior they should return ErrNode.NUM (`#NUM`!); update the error returns in
DISC (discountFunc), YIELDDISC (yielddiscFunc), RECEIVED (receivedFunc), INTRATE
(intrateFunc), PRICEMAT (pricematFunc), YIELDMAT (yieldmatFunc), PDURATION
(pdurationFunc) and RRI (rriFunc) to return ErrNode.NUM where the code currently
returns ErrNode.VALUE (or does numeric-domain checks), and change
non-convergence/error paths in RATE, IRR, and XIRR implementations (functions
named rateFunc, irrFunc, xirrFunc) to return ErrNode.NUM instead of
ErrNode.VALUE; ensure you only replace the specific error return values and keep
the existing guard checks and control flow intact.

packages/sheets/src/formula/functions-engineering.ts (1)

352-367: ⚠️ Potential issue | 🟠 Major

Return #NUM! for DEC2 numeric-domain failures.*

These branches still map out-of-range inputs and too-small places to ErrNode.VALUE, so ERROR.TYPE remains inconsistent with the PR’s numeric-domain convention.

🔧 Proposed error-code alignment

-  if (val < -549755813888 || val > 549755813887) return ErrNode.VALUE;
+  if (val < -549755813888 || val > 549755813887) return ErrNode.NUM;
@@
-    if (p < hex.length) return ErrNode.VALUE;
+    if (p < hex.length) return ErrNode.NUM;
@@
-  if (val < -512 || val > 511) return ErrNode.VALUE;
+  if (val < -512 || val > 511) return ErrNode.NUM;
@@
-    if (p < bin.length) return ErrNode.VALUE;
+    if (p < bin.length) return ErrNode.NUM;
@@
-  if (val < -536870912 || val > 536870911) return ErrNode.VALUE;
+  if (val < -536870912 || val > 536870911) return ErrNode.NUM;
@@
-    if (p < oct.length) return ErrNode.VALUE;
+    if (p < oct.length) return ErrNode.NUM;

Also applies to: 410-425, 468-483

🤖 Prompt for AI Agents

Verify each finding against the current code and only fix it if needed.

In `@packages/sheets/src/formula/functions-engineering.ts` around lines 352 - 367,
The numeric-domain error handling in functions-engineering.ts must return
ErrNode.NUM (representing `#NUM`!) instead of ErrNode.VALUE for DEC2* conversions:
change the out-of-range check that currently returns ErrNode.VALUE when val <
-549755813888 || val > 549755813887 to return ErrNode.NUM, and change the
places-too-small branch (the check if p < hex.length) that returns ErrNode.VALUE
to return ErrNode.NUM; apply the same replacement in the other DEC2* blocks
referenced (the similar branches around the blocks handling ranges and places at
the locations corresponding to the other diffs, e.g. the blocks around lines
410-425 and 468-483) while leaving existing early error propagation (places.t
=== 'err') unchanged.

packages/sheets/src/formula/functions-statistical.ts (2)

1266-1305: ⚠️ Potential issue | 🟠 Major

Guard single-value PERCENTRANK arrays before dividing by n - 1.

With one numeric value and x equal to that value, rank = smaller / (n - 1) becomes 0 / 0, returning { t: 'num', v: NaN } instead of a spreadsheet error.

🛡️ Proposed guard

   const vals = collectNumericValues(exprs[0], visit, grid);
   if (!Array.isArray(vals)) return vals;
   if (vals.length === 0) return ErrNode.NA;
+  if (vals.length < 2) return ErrNode.DIV0;

🤖 Prompt for AI Agents

Verify each finding against the current code and only fix it if needed.

In `@packages/sheets/src/formula/functions-statistical.ts` around lines 1266 -
1305, The PERCENTRANK implementation can divide by zero when vals has a single
numeric value (n === 1); add a guard after computing n (and before any division
by n - 1) to detect n === 1 and return a spreadsheet error (e.g. ErrNode.DIV0)
so we don't compute rank = smaller / (n - 1) or do interpolation; update the
block surrounding vars n, smaller, idx, and rank in functions-statistical.ts to
return ErrNode.DIV0 when n === 1.

---

3106-3124: ⚠️ Potential issue | 🟠 Major

Avoid NaN from Z.TEST when sigma is omitted for a single sample.

For one data point without an explicit sigma, the sample variance divides by dataResult.length - 1 (0), producing NaN and returning it as a numeric result. Also reject non-positive explicit sigma before using it as a divisor.

🛡️ Proposed guard

   const dataResult = collectNumericValues(exprs[0], visit, grid);
   if (!Array.isArray(dataResult)) return dataResult;
   if (dataResult.length === 0) return ErrNode.VALUE;
@@
   if (exprs.length >= 3) {
     const s = NumberArgs.map(visit(exprs[2]), grid);
     if (s.t === 'err') return s;
     sigma = s.v;
+    if (sigma <= 0) return ErrNode.VALUE;
   } else {
+    if (dataResult.length < 2) return ErrNode.DIV0;
     // Sample standard deviation
     const variance = dataResult.reduce((a, b) => a + (b - mean) ** 2, 0) / (dataResult.length - 1);
     sigma = Math.sqrt(variance);
+    if (sigma === 0) return ErrNode.DIV0;
   }

🤖 Prompt for AI Agents

Verify each finding against the current code and only fix it if needed.

In `@packages/sheets/src/formula/functions-statistical.ts` around lines 3106 -
3124, The Z.TEST implementation can produce NaN when sigma is omitted for a
single sample and must reject non-positive explicit sigma; update the block in
the function using collectNumericValues, exprs, NumberArgs.map and sigma so that
if dataResult.length - 1 <= 0 (i.e. single data point and no explicit sigma) you
return ErrNode.DIV0 (or the project's appropriate divide-by-zero error) instead
of computing variance, and if an explicit sigma (from
NumberArgs.map(visit(exprs[2]))) is provided validate that s.v is finite and > 0
and return ErrNode.DIV0 (or ErrNode.NUM per project convention) when not, before
using sigma as a divisor; keep the rest of the z calculation and final return
using normCdf unchanged.

packages/sheets/src/formula/functions-math.ts (3)

2084-2120: ⚠️ Potential issue | 🟠 Major

AGGREGATE statistical error paths diverge from SUBTOTAL and Google Sheets.

SUBTOTAL now correctly returns ErrNode.DIV0 when there aren't enough values for STDEV/STDEVP/VAR/VARP (lines 2018, 2025, 2034, 2040), matching Google Sheets. The same cases inside AGGREGATE (func nums 7, 8, 10, 11) still return ErrNode.VALUE, so e.g. AGGREGATE(7, 0, A1) vs. SUBTOTAL(7, A1) on a single value will report different ERROR.TYPE codes for the same underlying cause.

Also, the default branch (line 2119) is hit for an unknown function_num; Google Sheets returns #VALUE! there, which is fine, but note that 12 (MEDIAN) is the only extra entry — if you intend to support 13–19 (MODE/LARGE/SMALL/PERCENTILE/QUARTILE/…) the fall-through is silently wrong.

🛠️ Proposed fix for the insufficient-sample cases

     case 7: { // STDEV.S
-      if (nums.length < 2) return ErrNode.VALUE;
+      if (nums.length < 2) return ErrNode.DIV0;
       const mean = nums.reduce((a, b) => a + b, 0) / nums.length;
       const variance = nums.reduce((a, b) => a + (b - mean) ** 2, 0) / (nums.length - 1);
       return { t: 'num', v: Math.sqrt(variance) };
     }
     case 8: { // STDEV.P
-      if (nums.length === 0) return ErrNode.VALUE;
+      if (nums.length === 0) return ErrNode.DIV0;
       const mean = nums.reduce((a, b) => a + b, 0) / nums.length;
       const variance = nums.reduce((a, b) => a + (b - mean) ** 2, 0) / nums.length;
       return { t: 'num', v: Math.sqrt(variance) };
     }
     case 9: return { t: 'num', v: nums.reduce((a, b) => a + b, 0) }; // SUM
     case 10: { // VAR.S
-      if (nums.length < 2) return ErrNode.VALUE;
+      if (nums.length < 2) return ErrNode.DIV0;
       const mean = nums.reduce((a, b) => a + b, 0) / nums.length;
       return { t: 'num', v: nums.reduce((a, b) => a + (b - mean) ** 2, 0) / (nums.length - 1) };
     }
     case 11: { // VAR.P
-      if (nums.length === 0) return ErrNode.VALUE;
+      if (nums.length === 0) return ErrNode.DIV0;
       const mean = nums.reduce((a, b) => a + b, 0) / nums.length;
       return { t: 'num', v: nums.reduce((a, b) => a + (b - mean) ** 2, 0) / nums.length };
     }

🤖 Prompt for AI Agents

Verify each finding against the current code and only fix it if needed.

In `@packages/sheets/src/formula/functions-math.ts` around lines 2084 - 2120, The
AGGREGATE switch (variable fn) currently returns ErrNode.VALUE for
insufficient-sample errors in cases 7 (STDEV.S), 8 (STDEV.P), 10 (VAR.S) and 11
(VAR.P); change those early-return branches to return ErrNode.DIV0 to match
SUBTOTAL/Google Sheets behavior, e.g. replace "if (nums.length < 2) return
ErrNode.VALUE" with "return ErrNode.DIV0" in case 7 and 10, and replace "if
(nums.length === 0) return ErrNode.VALUE" with "return ErrNode.DIV0" in case 8
and 11; keep the rest of the calculations unchanged. Also confirm whether you
intend to implement functions 13–19 (MODE/LARGE/SMALL/PERCENTILE/QUARTILE/etc.)
because MEDIAN (12) is currently the only extra implemented item and any missing
function_nums will fall through to the default ErrNode.VALUE.

---

2359-2372: ⚠️ Potential issue | 🟠 Major

LOG10 must return #NUM! for non-positive input, not #VALUE!.

LN and LOG both return ErrNode.NUM when their arguments are ≤ 0, but LOG10 incorrectly returns ErrNode.VALUE for the same condition. Google Sheets returns #NUM! for both LOG10(0) and LOG10(-1), making this an error-type mismatch that breaks consistency across the logarithm functions.

Fix

-  if (n.v <= 0) return ErrNode.VALUE;
+  if (n.v <= 0) return ErrNode.NUM;

🤖 Prompt for AI Agents

Verify each finding against the current code and only fix it if needed.

In `@packages/sheets/src/formula/functions-math.ts` around lines 2359 - 2372,
log10Func currently returns ErrNode.VALUE for non-positive inputs but should
return ErrNode.NUM to match LN/LOG and Google Sheets behavior; locate the
function log10Func and change the branch that checks n.v <= 0 to return
ErrNode.NUM instead of ErrNode.VALUE (keeping the rest of the flow: obtain args
via ctx.args(), map the argument using NumberArgs.map(visit(exprs[0]), grid),
and preserve existing error propagation for n.t === 'err').

---

2270-2300: ⚠️ Potential issue | 🟡 Minor

Change ErrNode.VALUE to ErrNode.NUM for singular matrix detection.

At line 2281, the singular matrix detection returns ErrNode.VALUE, but Google Sheets reports #NUM! for MINVERSE of a singular square matrix. This is a numeric-domain failure (analogous to other #NUM! mappings throughout the codebase), not a type/shape error. The non-matrix/non-square branches at lines 2251/2254 correctly use ErrNode.VALUE.

🛠️ Proposed fix

-    if (maxVal < 1e-12) return ErrNode.VALUE; // Singular
+    if (maxVal < 1e-12) return ErrNode.NUM; // Singular matrix → `#NUM`!

🤖 Prompt for AI Agents

Verify each finding against the current code and only fix it if needed.

In `@packages/sheets/src/formula/functions-math.ts` around lines 2270 - 2300, In
the Gauss-Jordan elimination inside the MINVERSE implementation, the
singular-matrix check currently returns ErrNode.VALUE; change that to
ErrNode.NUM so a singular square matrix yields the numeric error used for
MINVERSE; specifically replace the return at the pivot-check (the spot using
maxVal < 1e-12 within the loop in the Gauss-Jordan code that references aug,
maxRow/maxVal and pivot) to return ErrNode.NUM instead of ErrNode.VALUE.