Using the default column denominator
relative_to=0 computes a share of each column. The task asks every intersection to use one grand denominator.
Report each group share of net units
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. If their signed grand sum is zero, return No net units in one cell. Otherwise return an empty-text corner, sorted channel headings and sorted region rows. Each observed intersection is its signed sum divided by the same grand signed sum; a missing intersection is empty text. Return decimal proportions, no row or column totals. Shares may be negative or exceed one.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:O13. 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
Make the grand-total denominator explicit, retaining signed returns, missing intersections and a zero-net state.
THE QUICK ANSWER01
Make the grand-total denominator explicit, retaining signed returns, missing intersections and a zero-net state.
ƒx
=IF(SUMPRODUCT((D2:D9="Posted")*C2:C9)=0,"No net units",PIVOTBY(A2:A9,B2:B9,C2:C9,PERCENTOF,0,0,1,0,1,D2:D9="Posted",2))| G | H | I |
|---|---|---|
| "" | STORE | WEB |
| EAST | 0.1724137931034483 | 0.5517241379310345 |
| WEST | 0.27586206896551724 | 0 |
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
Declare the whole before reading a part
The first source has net posted units 29. EAST/WEB contributes 16/29, EAST/STORE 5/29 and WEST/STORE 8/29. WEST/WEB has a real zero share because its posted observation is zero.
Zero net differs from no activity
The cancellation source contains signed posted units but has a zero grand net. No net units describes the undefined denominator; it does not claim there were no posted records. When net units equal one, signed intersection shares can be very large.
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
- Calculate the same posted grand sum
The status mask excludes draft and void amounts from the denominator as well as from the grouped report.
- Handle a zero denominator explicitly
A zero grand net can occur with many posted records. Return No net units instead of inventing zero shares.
- Aggregate signed intersection amounts
PERCENTOF receives the intersection observations and a denominator population. It divides their sums, rather than counting rows.
- Use the grand denominator
relative_to=2 supplies the complete posted population as the second input. Modes 0 and 1 answer column-share and row-share questions.
- Read proportions with their signs
Return proportions such as 16/29. Returns may create negative shares or a share above one; multiplying by 100 changes the stored result.
Things to watch
Removing negative units
Returns affect both numerator and denominator. Dropping them answers a different business question.
Returning zero for a zero denominator
Zero shares would suggest measured proportions. Return the declared No net units state instead.
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.