Averaging visible cell averages
A mean of displayed means gives equal weight to groups with different row counts. Recalculate totals from the original observations.
Cross-tabulate posted order means
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. Use only Posted rows. Sort regions and channels ascending. Return an empty-text corner, channel headings, one mean-units row per region, a final Total column and a final Total row. Calculate each intersection and each total from its own original posted observations. A missing region/channel combination is empty text; a posted zero is numeric zero. If no row is Posted, return No posted records.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:P14. 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
Build a region-by-channel mean report whose row, column and grand means use the original records.
THE QUICK ANSWER01
Build a region-by-channel mean report whose row, column and grand means use the original records.
ƒx
=IF(SUMPRODUCT(--(D2:D9="Posted"))=0,"No posted records",PIVOTBY(A2:A9,B2:B9,C2:C9,AVERAGE,0,1,1,1,1,D2:D9="Posted"))| G | H | I | J |
|---|---|---|---|
| "" | STORE | WEB | Total |
| EAST | 5 | 5.333333333333333 | 5.25 |
| WEST | 8 | 0 | 4 |
| Total | 6.5 | 4 | 4.833333333333333 |
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
Weight totals by original record counts
In the first source EAST/WEB has 12, -2 and 6, giving 16/3. EAST/STORE is 5. EAST total is 21/4, not the mean of 16/3 and 5. The grand mean is 29/6.
Read the total labels with the reducer
With AVERAGE, Total is the mean over the corresponding source subset. It is not a sum. The report retains numeric zeroes and uses empty text only for intersections with no observation.
Keep the declared source types
Use eight source rows. Region and channel labels are uppercase ASCII tokens beginning with a letter, followed by letters, digits or hyphens, up to 20 characters. Units are whole numbers within ±1,000,000. Posted, Draft and Void are the allowed statuses. Returns stay negative; zero is a measured numeric value.
Reserve room for a changing report
Enter one formula in G5 and keep the whole graded output area clear. Axis counts change between sources. A formula spill updates after source edits; it is separate from a native PivotTable object.
Read the full step-by-step walkthrough
- Filter the observation population
Posted records define both axes and all averages. Draft and Void rows cannot introduce a new channel or region.
- Choose two independent axes
Regions form output rows and channels form output columns. Their order is declared ascending.
- Reduce original observations
AVERAGE divides the sum of numeric units by the number of observations in each intersection. Repeated orders count separately.
- Recompute each total mean
Set both total depths to 1. A row, column or grand total uses its complete selected source subset, which may have unequal group sizes.
- Distinguish absence from zero
A missing intersection has no posted observation and displays empty text. An observed zero remains numeric and participates in means.
Things to watch
Replacing empty intersections with zero
This adds an observation that never existed. Keep empty text distinct from a posted numeric zero.
Including headers as observations
Use A2:A9, B2:B9 and C2:C9 with field_headers=0. The source heading row is excluded.
About this example
Original bounded whole-unit sources. Browser formulas and complete spill shapes are checked against separate source-only rules. Native Excel/Sheets recalculation and edit/save remain separate checks. PIVOTBY reports are formula spills rather than PivotTable objects.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.