Numeric text is silently converted
Reference-based COUNT and AVERAGE ignore stored text. AVERAGEA includes referenced text as zero rather than converting its spelling into a number.
Count numbers, text, logical values and blank-like cells
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter B13 as a real IF controlled by Boolean H2: TRUE returns empty text and FALSE returns Recorded. Keep B2:B12 as eleven editable stored observations. Fill F2:F13 separately. F2 counts physical cells, F3 numeric values, F4 nonempty cells, F5 true blanks plus formula empty text, F6 text, F7 logical values and F8 true blanks only. F9 reports the overlap F4+F5-F2. F10:F12 count numeric zeroes, positives and negatives without coercing text or Booleans. F13 sums the disjoint numeric/text/logical/true-blank counts and must reconcile with physical slots. Source numbers and the threshold are finite within ±1,000,000; editable text is nonempty text up to 40 Unicode characters, including spaces and numeric/error-looking text. Empty text is produced by the real B13 formula; use Blank for an actual empty source cell. Typed formula errors are outside this error-free comparison. These are reference-range rules, not direct-argument coercion rules.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: B13, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13. Browser calculations may differ from your Excel version.
Inspect current stored results and formulas
| Cell | Current value | Applied formula |
|---|---|---|
| B13 | — | No formula |
| F2 | — | No formula |
| F3 | — | No formula |
| F4 | — | No formula |
| F5 | — | No formula |
| F6 | — | No formula |
| F7 | — | No formula |
| F8 | — | No formula |
| F9 | — | No formula |
| F10 | — | No formula |
| F11 | — | No formula |
| F12 | — | No formula |
| F13 | — | No formula |
Change an actual source value
Edit eleven actual observations as Number, Text, Boolean or Blank. Nonempty text up to 40 characters retains numeric-looking spelling and whitespace. H2 is a Boolean controlling the real B13 empty-text formula; the mean task also has numeric threshold H3. Numbers stay within ±1,000,000. Typed formula errors have a separate lesson.
This draft matches the current cell.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Reconcile four disjoint stored types and explain why COUNTA plus COUNTBLANK can exceed the number of cells.
THE QUICK ANSWER01
Reconcile four disjoint stored types and explain why COUNTA plus COUNTBLANK can exceed the number of cells.
B13
=IF(H2,"","Recorded")F2
=ROWS($B$2:$B$13)F3
=COUNT($B$2:$B$13)F4
=COUNTA($B$2:$B$13)F5
=COUNTBLANK($B$2:$B$13)F6
=SUMPRODUCT(--ISTEXT($B$2:$B$13))F7
=SUMPRODUCT(--ISLOGICAL($B$2:$B$13))F8
=SUMPRODUCT(--ISBLANK($B$2:$B$13))F9
=F4+F5-F2F10
=SUMPRODUCT(--ISNUMBER($B$2:$B$13),--(IF(ISNUMBER($B$2:$B$13),$B$2:$B$13,0)=0))F11
=SUMPRODUCT(--ISNUMBER($B$2:$B$13),--(IF(ISNUMBER($B$2:$B$13),$B$2:$B$13,0)>0))F12
=SUMPRODUCT(--ISNUMBER($B$2:$B$13),--(IF(ISNUMBER($B$2:$B$13),$B$2:$B$13,0)<0))F13
=SUM(F3,F6:F8)For the example data
B13 F2 12F3 4F4 10F5 3F6 4F7 2F8 2F9 1F10 1F11 2F12 1F13 12
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
Stored types determine the population
A numeric-looking identifier remains text. This report uses actual stored numbers, text, logical values and true blanks; neither formatting nor an empty display changes a stored type.
Empty text and a true blank are different
B13 has a real formula even when it displays nothing. COUNTA includes that cell and COUNTBLANK includes its empty result. ISBLANK remains false. The eleven editable slots can be genuinely cleared.
Choose the question before the function
The physical slot count, numeric count, nonempty count and blank-like count answer different questions. Likewise a numeric mean including zero, nonzero mean, strict-threshold mean and AVERAGEA policy need not agree.
Keep source changes and result checks connected
Every report is checked against current source types, eight original populations and independent type/control/threshold changes. The current workbook downloads preserve typed source values, the B13 formula and complete live results.
Read the full step-by-step walkthrough
- Create empty text without deleting the cell
H2 controls the real B13 formula. TRUE displays empty text; FALSE displays Recorded. Select B13 in the formula bar to distinguish an empty-looking formula from a true blank.
- Count numeric observations rather than physical rows
ROWS counts all twelve slots. COUNT includes numeric zeroes and ignores referenced numeric text, ordinary text, logical values and empty cells.
- Compare nonempty and blank-like counts
COUNTA includes the B13 empty-text formula. COUNTBLANK also includes its empty result, while numeric zero remains nonblank. These two counts are not disjoint complements.
- Partition stored types with explicit tests
ISTEXT includes whitespace and numeric-looking text. ISLOGICAL isolates TRUE/FALSE; ISBLANK counts only genuinely empty source cells. Inspect these types without rewriting the source.
- Measure the empty-text overlap
Nonempty plus blank-like counts can exceed physical slots because an empty-text formula belongs to both. Toggle H2 to remove the overlap without clearing any source cell.
- Separate numeric zero, positive and negative values
ISNUMBER masks protect the three numeric sign counts. A stored text 0 or FALSE must not become a numeric zero merely through comparison coercion.
- Reconcile the disjoint type populations
Numbers, text, logical values and true blanks partition all twelve slots in this error-free report. Their sum must equal ROWS even when a COUNTBLANK-based partition would double-count the empty-text formula.
Things to watch
An empty display means an empty cell
Inspect the actual formula in B13. Its empty text is counted by both COUNTA and COUNTBLANK, but not by ISBLANK.
Zero is treated as missing
Numeric zero belongs in an ordinary numeric mean; a nonzero policy must be explicit.
A copied formula is used for every result
These heterogeneous report rows answer different questions and require separate formulas.
About this example
Original bounded error-free reference population tasks. Browser/module/package checks do not certify native Excel/Sheets recalculation, rendering or edit/save.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.