Dynamic Arrays
Transpose a table without zeros: suppress blank-to-0 coercion with LET & IF
Extract a coordinate from a transposed numeric grid and display a blank string for either an empty source cell or an explicit zero.
Choose whether zero values should be hidden
This presentation example intentionally hides both empty source values and genuine numeric zeros. A zero can mean a measured result, so do not suppress it when readers need to distinguish zero from missing data. Negative and positive numbers remain visible.
The formula below returns one coordinate from the transposed 3-by-3 grid. E2 selects the output row and F2 the output column; both must be integers from 1 through 3. An empty string returned by a formula is not a physically empty cell, and COUNTA can still count it. Do not use zero suppression as a data-cleaning or missing-data rule.
| Source Grid (Rows x Cols) | Raw TRANSPOSE Output | Zero-Suppressed Output | Cell Coordinate |
|---|---|---|---|
| A2 = 120, B2 = "" (blank), C2 = 85 | Col 1: [120; 0; 85] | Col 1: [120; ""; 85] | Row 2, Col 1 -> "" |
| A3 = 0, B3 = 90, C3 = "" | Col 2: [0; 90; 0] | Col 2: [""; 90; ""] | Row 2, Col 2 -> 90 |
| A4 = 150, B4 = 110, C4 = 95 | Col 3: [150; 110; 95] | Col 3: [150; 110; 95] | Row 3, Col 3 -> 95 |
Extract zero-suppressed transposed cell at (row, col)
LET evaluates the transposed lookup once so you never write TRANSPOSE twice inside IF.
=IF(COUNT(E2:F2)<>2,"Invalid coordinates",IF(OR(E2<1,E2>3,F2<1,F2>3,E2<>INT(E2),F2<>INT(F2)),"Invalid coordinates",LET(v, INDEX(TRANSPOSE(A2:C4), E2, F2), IF(v = 0, "", v))))Count positive source values only
COUNTIF with >0 counts positive numeric values in the source, not all entries that survive suppression. Negative values remain visible but are not included in this count.
=COUNTIF(A2:C4, ">0")Try the example
Use the original values or change them to test your understanding.