COUNT or COUNTA decides the endpoint
An interior blank changes nonempty counts but must not change the explicit physical boundary.
Compare OFFSET and INDEX dynamic ranges
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Keep all eight records in place. G2 is a stored integer from 1 through 8 and selects exactly the first N physical rows starting at row 2, including any blanks and numeric zeroes. Build G5:G9 with actual OFFSET references and H5:H9 with actual INDEX endpoints. Compare each corresponding result in I5:I9. Amount and units totals ignore true blanks; the amount mean includes numeric zero and ignores true blanks. If no selected amount is numeric, return No numbers for both mean methods. Physical rows is N; Numeric amounts counts the selected numeric amount cells. Measures are stored finite whole-cent numbers within ±1,000,000 or genuine blank cells. Text and Booleans are not source measures for this exercise. These independent report formulas must not be filled over one another.
Change an actual source value
Change G2 to select 1–8 physical rows, or edit numeric/blank source measures. Apply the stored value, then inspect both recalculated reference methods.
This draft matches the current cell.
Review the stored row count in G2.
| Cell | Applied formula | Calculated value |
|---|---|---|
| G5 | No formula | — |
| H5 | No formula | — |
| I5 | No formula | — |
| G6 | No formula | — |
| H6 | No formula | — |
| I6 | No formula | — |
| G7 | No formula | — |
| H7 | No formula | — |
| I7 | No formula | — |
| G8 | No formula | — |
| H8 | No formula | — |
| I8 | No formula | — |
| G9 | No formula | — |
| H9 | No formula | — |
| I9 | No formula | — |
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5, H5, I5, G6, H6, I6, G7, H7, I7, G8, H8, I8, G9, H9, I9. 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
Use one row-count input to resize two real reference methods, then compare their totals, means and selected populations.
THE QUICK ANSWER01
OFFSET creates a reference of height G2 from B2. B2:INDEX(B2:B9,G2) creates the same inclusive range through the Nth source cell.
G5
=SUM(OFFSET($B$2,0,0,$G$2,1))H5
=SUM($B$2:INDEX($B$2:$B$9,$G$2))I5
=G5=H5G6
=SUM(OFFSET($C$2,0,0,$G$2,1))H6
=SUM($C$2:INDEX($C$2:$C$9,$G$2))I6
=G6=H6G7
=IF(COUNT(OFFSET($B$2,0,0,$G$2,1))=0,"No numbers",AVERAGE(OFFSET($B$2,0,0,$G$2,1)))H7
=IF(COUNT($B$2:INDEX($B$2:$B$9,$G$2))=0,"No numbers",AVERAGE($B$2:INDEX($B$2:$B$9,$G$2)))I7
=G7=H7G8
=ROWS(OFFSET($B$2,0,0,$G$2,1))H8
=ROWS($B$2:INDEX($B$2:$B$9,$G$2))I8
=G8=H8G9
=COUNT(OFFSET($B$2,0,0,$G$2,1))H9
=COUNT($B$2:INDEX($B$2:$B$9,$G$2))I9
=G9=H9For the example data
G5 9H5 9I5 trueG6 5H6 5I6 trueG7 3H7 3I7 trueG8 4H8 4I8 trueG9 3H9 3I9 true
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
A row count is an explicit inclusion policy
A blank in the third amount cell does not authorize shifting the endpoint. For the first four demo rows the amount total is 9, the amount mean is 3, physical rows is 4 and numeric amounts is 3. The later 40 belongs outside that selected prefix.
References are consumed by an aggregation
Both methods describe a range. SUM and AVERAGE consume its cells; ROWS reads its geometry. The same reference can therefore support different questions without replacing the source data.
Agreement needs an independent check
Two formulas can agree while both include every record or both exclude the first one. This task tests complete paired results under different controls, in-range values, excluded values and blank patterns.
This is not automatic last-row discovery
G2 is a chosen physical row count, not COUNTA of a possibly sparse column. Adding populated cells below the selected boundary does not include them until the control grows. Workbook Tables and automatic append behavior are separate workflows.
Read the full step-by-step walkthrough
- Choose the physical boundary
G2 selects the first N physical rows. It is not a count of nonblank cells. The highlighted amount range follows this actual input; a blank inside it still occupies a row.
- Describe the rectangle with OFFSET
Start at B2, move zero rows and columns, request G2 rows and one column. SUM consumes the returned reference; OFFSET does not move source records.
- Build the endpoint with INDEX
INDEX selects the Nth position inside B2:B9. The colon joins B2 to that returned reference. The endpoint is included; selecting four rows ends at B5, not B4 or B6.
- Change the measure, keep the boundary
Apply the same two reference methods to Signed units. Changing the amount source cannot change the independent units total.
- Separate mean, rows and numeric count
The mean divides by selected numeric amounts, including zero. ROWS still includes blank positions. COUNT can therefore be smaller than ROWS. No selected numeric amount produces No numbers rather than an invented zero mean.
- Compare actual paired results
I5:I9 compare corresponding calculated results. TRUE agreement alone does not prove the selected range is correct, so each method is checked against the source and changing controls. Change G2, then edit a cell inside and outside the boundary.
Things to watch
The last row is one position too far
Position 1 of B2:B9 is B2; position N ends at row N+1.
A matching pair is considered proof
The two methods must independently match the declared source prefix, not just one another.
Blank and zero have the same mean effect
A zero is a numeric observation. A true blank is not in the AVERAGE denominator.
About this example
Original bounded paired-reference task and current browser copies; no native-client recalculation or performance certification.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.