An unobserved row appears as zero
Use an explicit No observations result when COUNT is zero.
Return one numeric review mean for every record
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 to return eight record labels and review means in G5:H12. Average only numeric B:D ratings for each source row. Zero is an observation; blanks and text, including text numbers, are ignored. A row with no numeric rating returns No observations. Keep source order and repeated labels.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:H12. 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
Use BYROW to reduce each three-review row, then keep the record labels beside the results with HSTACK.
THE QUICK ANSWER01
Use BYROW to reduce each three-review row, then keep the record labels beside the results with HSTACK.
ƒx
=HSTACK(A2:A9,BYROW(B2:D9,LAMBDA(samples,IF(COUNT(samples)=0,"No observations",AVERAGE(samples)))))| Record | Numeric review mean |
|---|---|
| R1 | 8 |
| R2 | 3 |
| R3 | No observations |
| R4 | 3 |
| R5 | 10 |
| R6 | 0 |
| R7 | 3 |
| R8 | 6 |
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
Zero differs from missing
The original R2 row contains zero and six, so its mean is three. R3 has no numeric review and returns No observations. R6 has three measured zeroes and returns numeric zero.
Text numbers remain text
R4 contains the text 8 plus numeric two and four, so its mean is three. This task does not infer or convert imported values. Convert text numbers deliberately in a separate preparation step if that is the intended rule.
One row, one scalar result
BYROW’s LAMBDA must return one summary for each input row. HSTACK combines those eight summaries with eight labels. Repeated labels remain repeated; this is not a grouped customer average.
Supported review inputs
Record labels are nonempty ASCII text up to 40 characters. Review values may be numeric ratings from zero through ten, blank cells or text notes. Boolean values and ratings outside that range are left ungraded. The three review columns belong to each source row; no weighting is applied.
Read the full step-by-step walkthrough
- Choose the row being summarised
BYROW passes one B:D review row to the LAMBDA. It returns one value per source row, rather than one average for the whole table.
- Count numeric observations
COUNT includes numeric zero and ignores text and blanks. The text 8 in the fourth source record is not converted into a rating.
- Keep an unobserved row explicit
A row with no numeric rating returns No observations. Zero would incorrectly look like an observed mean.
- Average the values that were measured
AVERAGE receives the same row. For the original second record, zero and six give three; the missing review does not add a third observation.
- Attach the original record labels
HSTACK places the original labels next to the eight row summaries. Both arrays have eight rows, so their positions stay aligned. Repeated labels are separate records in this task.
- Keep the two-column spill clear
One anchor produces the complete report. The checker verifies labels, values, text states and source order on every dataset.
Things to watch
Text 8 changes the mean
Text notes and text numbers are ignored by this task. Do not coerce every review with VALUE or double unary.
Repeated labels are merged
The output is row-wise and preserves the original order. It does not group by record label.
About this example
The single anchor formula must spill into a clear output area. Local browser calculation and independently defined task rules are checked separately from native Excel/Google Sheets compatibility. Use a desktop Excel version supporting all named functions to recalculate a download; native editing/save/reopen is unverified.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.