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.
Compare mean populations without changing stored types
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/F3 are numeric count/sum. F4 averages numeric observations including zeroes; F5 checks the same sum/count. F6 excludes numeric zeroes and F7 averages only numeric observations strictly above numeric H3. F8 uses AVERAGEA: every referenced text contributes zero, TRUE contributes one and FALSE zero, while true blanks are ignored. F9 exposes its nonempty denominator. F10/F11 count the actual nonzero/qualifying numeric populations. F12 compares numeric means within 1e-9; F13 independently reconciles the threshold mean. Empty numeric populations return No numbers, No nonzero numbers or No qualifying numbers as specified. 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
Keep numeric zeroes, excluded text and explicit mean denominators visible before choosing an averaging rule.
THE QUICK ANSWER01
Keep numeric zeroes, excluded text and explicit mean denominators visible before choosing an averaging rule.
B13
=IF(H2,"","Recorded")F2
=COUNT($B$2:$B$13)F3
=SUM($B$2:$B$13)F4
=IF(F2=0,"No numbers",AVERAGE($B$2:$B$13))F5
=IF(F2=0,"No numbers",F3/F2)F6
=IFERROR(AVERAGEIF($B$2:$B$13,"<>0"),"No nonzero numbers")F7
=IFERROR(AVERAGEIF($B$2:$B$13,">"&H3),"No qualifying numbers")F8
=AVERAGEA($B$2:$B$13)F9
=COUNTA($B$2:$B$13)F10
=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)>H3))F12
=IF(F2=0,"No numbers",ABS(F4-F5)<1E-9)F13
=IF(F11=0,"No qualifying numbers",SUMPRODUCT(IF(ISNUMBER($B$2:$B$13),$B$2:$B$13,0),--(IF(ISNUMBER($B$2:$B$13),$B$2:$B$13,0)>H3))/F11)For the example data
B13 F2 4F3 12F4 3F5 3F6 4F7 7F8 1.3F9 10F10 3F11 2F12 trueF13 7
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
- Inspect the type before choosing the denominator
Numeric-looking text is not converted. B13 is an actual formula, so an empty result remains a text observation for AVERAGEA rather than a true empty cell.
- Average all numeric observations, including zero
COUNT defines the numeric denominator. AVERAGE and the separate SUM/COUNT must agree; a numeric zero is measured data and stays included.
- Exclude zero only when that is the intended population
The nonzero mean is a different question. Text 0 and FALSE remain excluded as nonnumeric references. A population containing only numeric zeroes has no nonzero mean.
- Compare a strict threshold with its actual count
Only numeric observations greater than H3 qualify. Equal values are excluded, including when H3 is negative. Inspect the count and independent arithmetic result alongside AVERAGEIF.
- Explain the AVERAGEA policy before using it
AVERAGEA uses a different denominator and numerator: referenced text, including numeric text and empty text, contributes zero; logical TRUE contributes one. True blanks are excluded. This is a policy, not an automatic data-cleaning step.
- Change the actual observations and reconcile again
Switch a number to text, clear a source cell or toggle H2. Compare every denominator and both independent mean checks; a previous passing result must not survive changed data.
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.