Keeping text numbers
The text 7 is not a numeric measurement under this task's type rule.
Flatten numeric readings in a declared scan order
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter one formula in G5. Return only the numeric cells from B2:D5 as one column. Ignore blanks, text (including text numbers) and booleans. Retain numeric zero and negative readings. F2=FALSE scans each row left to right, then moves down; F2=TRUE scans each column top to bottom, then moves right. Preserve that order without sorting. If there are no numeric readings, return No numeric readings in one cell.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:G16. Enter one formula in G5; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Turn a 4 by 3 readings grid into one column while keeping numeric zeroes and excluding text notes and booleans.
THE QUICK ANSWER01
Turn a 4 by 3 readings grid into one column while keeping numeric zeroes and excluding text notes and booleans.
ƒx
=IF(COUNT(B2:D5)=0,"No numeric readings",LET(items,TOCOL(B2:D5,1,F2),FILTER(items,ISNUMBER(items))))| G |
|---|
| 12 |
| 0 |
| -2 |
| 5 |
| 3 |
| 8 |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Read the first source in order
With F2=FALSE, the numeric list is 12, 0, -2, 5, 3 and 8. With F2=TRUE, it is 12, 0, -2, 5, 8 and 3. The text 7 and both Boolean cells are excluded in either direction.
Flattening has a cost
This output intentionally contains only numeric readings. It drops their batch and sensor coordinates. Use a labelled long-form report when each value must retain those identities.
Edit within the input contract
Reading cells allow finite numbers within ±1,000,000, ASCII notes up to 40 characters, booleans or blanks. F2 must be a Boolean scan control. Keep G5 and the reserved spill area clear.
Read the full step-by-step walkthrough
- Count numeric observations
COUNT reads numeric source cells, including zero and negative readings. Text numbers and Boolean cells do not become measurements.
- Choose the scan direction
TOCOL returns a column. Its third argument reads F2: FALSE scans by row; TRUE scans down each column.
- Remove blank source cells
The ignore value 1 skips blanks before the remaining values are tested. Text and Boolean values can still be present.
- Filter by numeric type
ISNUMBER supplies one Boolean per flattened value. FILTER keeps numeric zero and negative readings while excluding text numbers and booleans.
- Retain observed zeroes
Zero stays a numeric result. An empty source has the declared one-cell No numeric readings state.
Things to watch
Sorting the values
The output follows the selected source scan, not numeric order.
Removing zero or negative values
Both are valid numeric observations. Filter by type, not magnitude.
About this example
Original bounded source arrays; current browser calculations and complete spills are checked against independent source rules. Native client recalculation and edit/save are separate gates.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.