#VALUE! in Sequence and RandArray Formulas

A #VALUE! error in SEQUENCE or RANDARRAY indicates a data type incompatibility within the function’s input arguments. Unlike dynamic array sizing errors that trigger #SPILL! or boundary limit breaches that return #NUM!, a #VALUE! error specifically means that one or more required parameters received an invalid data type—such as text strings, blank values generated by formulas, or an array passed where a single scalar number was required.

Fast Fix

Verify that the rows and columns arguments contain positive scalar integers, not ranges or arrays. If referencing external cells for dimensions, ensure those cells are formatted as numbers and do not evaluate to empty text strings (""). Wrap cell references in VALUE() or use double unary operators (e.g., =SEQUENCE(--A2)) to coerce text numbers into integers.

---

Diagnostic Snapshot

Indicator	Detail
Visible Error	#VALUE!
Functions Affected	SEQUENCE, RANDARRAY
Common Misdiagnosis	Confusing #VALUE! (wrong data type) with #NUM! (invalid number/boundary) or #SPILL! (blocked output)
Top Root Cause 1	Passing an array/range into an argument that expects a single integer
Top Root Cause 2	Cell references returning empty strings ("") instead of numeric blanks or zeros
Top Root Cause 3	Text characters or non-convertible strings in optional arguments (step, min, max)

---

Error Distinction: #VALUE! vs. #NUM! vs. #SPILL!

To diagnose generation failures in dynamic arrays, distinguish between Excel’s different error codes:

• #NUM! (Numeric Boundary Failure): Occurs when the value is a number, but the number is mathematically or structurally illegal (e.g., =SEQUENCE(-5) for negative row count, or =RANDARRAY(10, 1, 100, 50) where min > max). For resolving numeric limit errors, see #NUM! Errors in Financial and Iterative Formulas.
• #SPILL! (Obstruction Failure): Occurs when the numbers are valid, but the array cannot draw its footprint because another cell blocks its path. Check #SPILL! Error – How to find the obstructing cell.
• #VALUE! (Data Type Failure): Occurs when the engine expects a single number, but receives non-numeric text, a boolean mask, an unhandled error, or a multi-cell vector.

---

Common Triggers and Targeted Solutions

1. Passing Multi-Cell Arrays into Dimension Arguments

Both SEQUENCE and RANDARRAY expect scalar integers for their dimension parameters:

• SEQUENCE(rows, [columns], [start], [step])
• RANDARRAY([rows], [columns], [min], [max], [whole_number])

If you inadvertently pass a range instead of a count:

• Incorrect: =SEQUENCE(A2:A10)
• Why it fails: A2:A10 is an array of 9 cells. SEQUENCE does not assume you mean “count the rows in A2:A10”; it attempts to evaluate the vector directly and crashes with #VALUE!.
• Corrected: Wrap the range inside ROWS() or COUNTA():
  =SEQUENCE(ROWS(A2:A10))

If you are feeding dynamic results into arguments and accidentally nest arrays within arrays, review Troubleshooting “Nested Array” Errors (Arrays inside Arrays).

2. The Empty String ("") Trap in Dependent Cells

A frequent source of #VALUE! errors occurs when SEQUENCE or RANDARRAY references a cell containing an IF formula:

Cell B2: =IF(C2="Custom", 10, "")

If C2 is not “Custom”, B2 returns "" (an empty text string).
When you write:

=SEQUENCE(B2)

Excel cannot evaluate =SEQUENCE(""). An empty text string is not a number; therefore, Excel triggers #VALUE!.

Resolution:
Update the upstream formula to return 0 instead of "", or sanitize the input using N():
=SEQUENCE(N(B2))(Note: If B2 is 0, SEQUENCE(0) will trigger #CALC! or an empty array, which can be handled with an IF check).

Upstream Evaluation Flow:
Cell C2: "Default"
  │
  ▼
Cell B2: =IF(C2="Custom", 10, "") ──> Returns "" (Text Data Type)
  │
  ▼
Formula: =SEQUENCE(B2)             ──> Passes Text to Integer Parameter
  │
  ▼
Output: #VALUE! (Data Type Conflict)

3. Text Coercion Failures

If numeric parameters are imported from CSV files, web exports, or accounting systems, they may be stored as text formatted numbers.

• =SEQUENCE("5") will often succeed because Excel attempts implicit type coercion.
• However, if the text contains hidden formatting, spaces, or currency symbols (e.g., " 5 " or "$10"), coercion fails and returns #VALUE!.

Resolution:
Strip characters and force numeric conversion:
=SEQUENCE(--TRIM(SUBSTITUTE(A2, "$", "")))

4. Invalid Boolean Values in RANDARRAY

The fifth argument of RANDARRAY controls integer vs. decimal generation:

=RANDARRAY(rows, cols, min, max, whole_number)

The whole_number argument requires a boolean value (TRUE for integers, FALSE for decimals) or numeric equivalents (1 or 0).

• If you pass a text string like "TRUE" (with quotes) or text descriptions like "Integer", the argument parser fails, throwing #VALUE!.
• Correct: =RANDARRAY(10, 1, 1, 100, TRUE) or =RANDARRAY(10, 1, 1, 100, 1)

---

Large-Scale Generation Hazards

When generating large dynamic sequences, ensure that valid dimensions do not exceed the outer limits of the worksheet grid. While requesting a negative row returns #NUM!, requesting an array that physically extends beyond row 1,048,576 triggers a spill boundary breakdown. If your formula parameters generate massive dimensions that destabilize sheet performance, consult Troubleshooting “Spill range is too big” (1M+ row limit).