Calculate fields from grouped source totals
Build a classic PivotTable calculation from Revenue, Cost and Units. Change its formula, inspect the original records and explain why a grand profit rate is not the average of row rates. Your data stays in this browser.
Teaching · read-only · Report!A1
Step 1 / 6Read complete original observations
Preparing the actual calculated-field worksheet…
Correct source/controls or explicitly restore edited calculation helpers. Previous valid results do not certify edited cells.
1. Choose and edit a source
One to fifty records. Amounts accept cents within ±1,000,000; Units accepts whole numbers within ±10,000. Category matching ignores case and preserves spaces, wildcards and error-looking labels as literal text.
Paste a complete source CSV or TSV
Use Record, Region, Product, Revenue, Cost and Units headings with plain decimal amounts.
| Record | Region | Product | Revenue | Cost | Units | Actions |
|---|---|---|---|---|---|---|
2. Define calculated fields
Load an original rule example, inspect its draft and apply the complete definition list.
Guard a threshold discount: rule notes
Re-evaluate the 1,000 revenue threshold at every intersection and total. IF keeps the unused zero-denominator branch from being evaluated.
Compare flags and scalar aggregators: rule notes
A direct reference to a Boolean helper is ignored by numeric aggregation. An evaluated Boolean expression is a scalar argument. COUNT(Revenue) counts one aggregate field value, not source records.
Use tiers, powers and aggregate fields: rule notes
SUM, COUNT and AVERAGE receive field sums here. They do not revisit individual source rows. Powers follow spreadsheet precedence: -2^2 is 4; 2^3^2 is 64.
Distinguish branch selection from logical evaluation: rule notes
IF and IFERROR select a branch. AND and OR evaluate their arguments, so a division error remains visible even when another condition already determines the Boolean result.
Write one Name = formula per line. Quote field names containing spaces, such as 'Profit Rate'. Define one to five fields, including references to other defined fields. Use + − * /, ^, comparisons (=, <>, <, >, <=, >=), TRUE/FALSE and ABS, ROUND, IFERROR, IF, AND, OR, NOT, SUM, COUNT, MIN, MAX and AVERAGE.
Definitions apply together. Invalid names, unknown fields, cycles, text strings, cell/range references and unlisted functions are withheld. These are calculated fields over source sums; calculated items, DAX and arbitrary Excel formula grammar are separate workflows.
3. Compare intersections and margins
8 of 8 records included. Select any value to inspect its source sums and records.
| region | Kit | Panel | Widget | Grand total |
|---|---|---|---|---|
| East | ||||
| North | ||||
| West | ||||
| Grand total |
Selected value: All rows / All columns
- Source Revenue sum
- 1,250
- Source Cost sum
- 940
- Source Units sum
- 14
Profit =Revenue-Cost- 310
Profit Rate ='Profit'/Revenue- 0.248
Revenue per Unit =Revenue/Units- 89.2857142857
Contributing records: P1, P2, P3, P4, P5, P6, P7, P8
Inspect original source rows
| ID | Source row | Region | Product | Revenue | Cost | Units |
|---|---|---|---|---|---|---|
| P1 | 2 | East | Kit | 100 | 10 | 1 |
| P2 | 3 | East | Kit | 900 | 810 | 9 |
| P3 | 4 | East | Panel | 200 | 80 | 2 |
| P4 | 5 | West | Kit | 0 | 25 | 0 |
| P5 | 6 | West | Panel | -100 | -60 | -1 |
| P6 | 7 | North | Panel | 50 | 75 | 1 |
| P7 | 8 | North | Kit | 0 | 0 | 2 |
| P8 | 9 | West | Widget | 100 | 0 | 0 |
Read every source-based check formula
| Cell | Formula | Independent result |
|---|---|---|
| K2 | =SUMPRODUCT($D$2:$D$9,--EXACT(LOWER($B$2:$B$9),LOWER("East")),--EXACT(LOWER($C$2:$C$9),LOWER("Kit"))) | 1,000 |
| L2 | =SUMPRODUCT($E$2:$E$9,--EXACT(LOWER($B$2:$B$9),LOWER("East")),--EXACT(LOWER($C$2:$C$9),LOWER("Kit"))) | 820 |
| M2 | =SUMPRODUCT($F$2:$F$9,--EXACT(LOWER($B$2:$B$9),LOWER("East")),--EXACT(LOWER($C$2:$C$9),LOWER("Kit"))) | 10 |
| N2 | =($K2-$L2) | 180 |
| O2 | =($N2/$K2) | 0.18 |
| P2 | =($K2/$M2) | 100 |
| T2 | =O2 | 0.18 |
| K3 | =SUMPRODUCT($D$2:$D$9,--EXACT(LOWER($B$2:$B$9),LOWER("East")),--EXACT(LOWER($C$2:$C$9),LOWER("Panel"))) | 200 |
| L3 | =SUMPRODUCT($E$2:$E$9,--EXACT(LOWER($B$2:$B$9),LOWER("East")),--EXACT(LOWER($C$2:$C$9),LOWER("Panel"))) | 80 |
| M3 | =SUMPRODUCT($F$2:$F$9,--EXACT(LOWER($B$2:$B$9),LOWER("East")),--EXACT(LOWER($C$2:$C$9),LOWER("Panel"))) | 2 |
| N3 | =($K3-$L3) | 120 |
| O3 | =($N3/$K3) | 0.6 |
| P3 | =($K3/$M3) | 100 |
| U2 | =O3 | 0.6 |
| K4 | =SUMPRODUCT($D$2:$D$9,--EXACT(LOWER($B$2:$B$9),LOWER("East")),--EXACT(LOWER($C$2:$C$9),LOWER("Widget"))) | 0 |
| L4 | =SUMPRODUCT($E$2:$E$9,--EXACT(LOWER($B$2:$B$9),LOWER("East")),--EXACT(LOWER($C$2:$C$9),LOWER("Widget"))) | 0 |
| M4 | =SUMPRODUCT($F$2:$F$9,--EXACT(LOWER($B$2:$B$9),LOWER("East")),--EXACT(LOWER($C$2:$C$9),LOWER("Widget"))) | 0 |
| N4 | =($K4-$L4) | 0 |
| O4 | =($N4/$K4) | #DIV/0! |
| P4 | =($K4/$M4) | #DIV/0! |
| V2 | =O4 | #DIV/0! |
| K5 | =SUMPRODUCT($D$2:$D$9,--EXACT(LOWER($B$2:$B$9),LOWER("East"))) | 1,200 |
| L5 | =SUMPRODUCT($E$2:$E$9,--EXACT(LOWER($B$2:$B$9),LOWER("East"))) | 900 |
| M5 | =SUMPRODUCT($F$2:$F$9,--EXACT(LOWER($B$2:$B$9),LOWER("East"))) | 12 |
| N5 | =($K5-$L5) | 300 |
| O5 | =($N5/$K5) | 0.25 |
| P5 | =($K5/$M5) | 100 |
| W2 | =O5 | 0.25 |
| K6 | =SUMPRODUCT($D$2:$D$9,--EXACT(LOWER($B$2:$B$9),LOWER("North")),--EXACT(LOWER($C$2:$C$9),LOWER("Kit"))) | 0 |
| L6 | =SUMPRODUCT($E$2:$E$9,--EXACT(LOWER($B$2:$B$9),LOWER("North")),--EXACT(LOWER($C$2:$C$9),LOWER("Kit"))) | 0 |
| M6 | =SUMPRODUCT($F$2:$F$9,--EXACT(LOWER($B$2:$B$9),LOWER("North")),--EXACT(LOWER($C$2:$C$9),LOWER("Kit"))) | 2 |
| N6 | =($K6-$L6) | 0 |
| O6 | =($N6/$K6) | #DIV/0! |
| P6 | =($K6/$M6) | 0 |
| T3 | =O6 | #DIV/0! |
| K7 | =SUMPRODUCT($D$2:$D$9,--EXACT(LOWER($B$2:$B$9),LOWER("North")),--EXACT(LOWER($C$2:$C$9),LOWER("Panel"))) | 50 |
| L7 | =SUMPRODUCT($E$2:$E$9,--EXACT(LOWER($B$2:$B$9),LOWER("North")),--EXACT(LOWER($C$2:$C$9),LOWER("Panel"))) | 75 |
| M7 | =SUMPRODUCT($F$2:$F$9,--EXACT(LOWER($B$2:$B$9),LOWER("North")),--EXACT(LOWER($C$2:$C$9),LOWER("Panel"))) | 1 |
| N7 | =($K7-$L7) | -25 |
| O7 | =($N7/$K7) | -0.5 |
| P7 | =($K7/$M7) | 50 |
| U3 | =O7 | -0.5 |
| K8 | =SUMPRODUCT($D$2:$D$9,--EXACT(LOWER($B$2:$B$9),LOWER("North")),--EXACT(LOWER($C$2:$C$9),LOWER("Widget"))) | 0 |
| L8 | =SUMPRODUCT($E$2:$E$9,--EXACT(LOWER($B$2:$B$9),LOWER("North")),--EXACT(LOWER($C$2:$C$9),LOWER("Widget"))) | 0 |
| M8 | =SUMPRODUCT($F$2:$F$9,--EXACT(LOWER($B$2:$B$9),LOWER("North")),--EXACT(LOWER($C$2:$C$9),LOWER("Widget"))) | 0 |
| N8 | =($K8-$L8) | 0 |
| O8 | =($N8/$K8) | #DIV/0! |
| P8 | =($K8/$M8) | #DIV/0! |
| V3 | =O8 | #DIV/0! |
| K9 | =SUMPRODUCT($D$2:$D$9,--EXACT(LOWER($B$2:$B$9),LOWER("North"))) | 50 |
| L9 | =SUMPRODUCT($E$2:$E$9,--EXACT(LOWER($B$2:$B$9),LOWER("North"))) | 75 |
| M9 | =SUMPRODUCT($F$2:$F$9,--EXACT(LOWER($B$2:$B$9),LOWER("North"))) | 3 |
| N9 | =($K9-$L9) | -25 |
| O9 | =($N9/$K9) | -0.5 |
| P9 | =($K9/$M9) | 16.6666666667 |
| W3 | =O9 | -0.5 |
| K10 | =SUMPRODUCT($D$2:$D$9,--EXACT(LOWER($B$2:$B$9),LOWER("West")),--EXACT(LOWER($C$2:$C$9),LOWER("Kit"))) | 0 |
| L10 | =SUMPRODUCT($E$2:$E$9,--EXACT(LOWER($B$2:$B$9),LOWER("West")),--EXACT(LOWER($C$2:$C$9),LOWER("Kit"))) | 25 |
| M10 | =SUMPRODUCT($F$2:$F$9,--EXACT(LOWER($B$2:$B$9),LOWER("West")),--EXACT(LOWER($C$2:$C$9),LOWER("Kit"))) | 0 |
| N10 | =($K10-$L10) | -25 |
| O10 | =($N10/$K10) | #DIV/0! |
| P10 | =($K10/$M10) | #DIV/0! |
| T4 | =O10 | #DIV/0! |
| K11 | =SUMPRODUCT($D$2:$D$9,--EXACT(LOWER($B$2:$B$9),LOWER("West")),--EXACT(LOWER($C$2:$C$9),LOWER("Panel"))) | -100 |
| L11 | =SUMPRODUCT($E$2:$E$9,--EXACT(LOWER($B$2:$B$9),LOWER("West")),--EXACT(LOWER($C$2:$C$9),LOWER("Panel"))) | -60 |
| M11 | =SUMPRODUCT($F$2:$F$9,--EXACT(LOWER($B$2:$B$9),LOWER("West")),--EXACT(LOWER($C$2:$C$9),LOWER("Panel"))) | -1 |
| N11 | =($K11-$L11) | -40 |
| O11 | =($N11/$K11) | 0.4 |
| P11 | =($K11/$M11) | 100 |
| U4 | =O11 | 0.4 |
| K12 | =SUMPRODUCT($D$2:$D$9,--EXACT(LOWER($B$2:$B$9),LOWER("West")),--EXACT(LOWER($C$2:$C$9),LOWER("Widget"))) | 100 |
| L12 | =SUMPRODUCT($E$2:$E$9,--EXACT(LOWER($B$2:$B$9),LOWER("West")),--EXACT(LOWER($C$2:$C$9),LOWER("Widget"))) | 0 |
| M12 | =SUMPRODUCT($F$2:$F$9,--EXACT(LOWER($B$2:$B$9),LOWER("West")),--EXACT(LOWER($C$2:$C$9),LOWER("Widget"))) | 0 |
| N12 | =($K12-$L12) | 100 |
| O12 | =($N12/$K12) | 1 |
| P12 | =($K12/$M12) | #DIV/0! |
| V4 | =O12 | 1 |
| K13 | =SUMPRODUCT($D$2:$D$9,--EXACT(LOWER($B$2:$B$9),LOWER("West"))) | 0 |
| L13 | =SUMPRODUCT($E$2:$E$9,--EXACT(LOWER($B$2:$B$9),LOWER("West"))) | -35 |
| M13 | =SUMPRODUCT($F$2:$F$9,--EXACT(LOWER($B$2:$B$9),LOWER("West"))) | -1 |
| N13 | =($K13-$L13) | 35 |
| O13 | =($N13/$K13) | #DIV/0! |
| P13 | =($K13/$M13) | 0 |
| W4 | =O13 | #DIV/0! |
| K14 | =SUMPRODUCT($D$2:$D$9,--EXACT(LOWER($C$2:$C$9),LOWER("Kit"))) | 1,000 |
| L14 | =SUMPRODUCT($E$2:$E$9,--EXACT(LOWER($C$2:$C$9),LOWER("Kit"))) | 845 |
| M14 | =SUMPRODUCT($F$2:$F$9,--EXACT(LOWER($C$2:$C$9),LOWER("Kit"))) | 12 |
| N14 | =($K14-$L14) | 155 |
| O14 | =($N14/$K14) | 0.155 |
| P14 | =($K14/$M14) | 83.3333333333 |
| T5 | =O14 | 0.155 |
| K15 | =SUMPRODUCT($D$2:$D$9,--EXACT(LOWER($C$2:$C$9),LOWER("Panel"))) | 150 |
| L15 | =SUMPRODUCT($E$2:$E$9,--EXACT(LOWER($C$2:$C$9),LOWER("Panel"))) | 95 |
| M15 | =SUMPRODUCT($F$2:$F$9,--EXACT(LOWER($C$2:$C$9),LOWER("Panel"))) | 2 |
| N15 | =($K15-$L15) | 55 |
| O15 | =($N15/$K15) | 0.366666666667 |
| P15 | =($K15/$M15) | 75 |
| U5 | =O15 | 0.366666666667 |
| K16 | =SUMPRODUCT($D$2:$D$9,--EXACT(LOWER($C$2:$C$9),LOWER("Widget"))) | 100 |
| L16 | =SUMPRODUCT($E$2:$E$9,--EXACT(LOWER($C$2:$C$9),LOWER("Widget"))) | 0 |
| M16 | =SUMPRODUCT($F$2:$F$9,--EXACT(LOWER($C$2:$C$9),LOWER("Widget"))) | 0 |
| N16 | =($K16-$L16) | 100 |
| O16 | =($N16/$K16) | 1 |
| P16 | =($K16/$M16) | #DIV/0! |
| V5 | =O16 | 1 |
| K17 | =SUMPRODUCT($D$2:$D$9) | 1,250 |
| L17 | =SUMPRODUCT($E$2:$E$9) | 940 |
| M17 | =SUMPRODUCT($F$2:$F$9) | 14 |
| N17 | =($K17-$L17) | 310 |
| O17 | =($N17/$K17) | 0.248 |
| P17 | =($K17/$M17) | 89.2857142857 |
| W5 | =O17 | 0.248 |
Query this visible value
GETPIVOTDATA reads this report’s active field, visible items and typed result. Source-field aliases are accepted. A hidden item, unknown field or conflicting item pair returns #REF!.
Current formula and fixed-value copies capture the actual working cells. Checked native copies require every current calculation to agree with its original source population. Query copies also check the current query. Native Excel opening, refresh and edit/save remain unverified; native source cells carry calculated facts rather than authored formulas.
4. Compare distinct numeric value fields
Place Revenue, Cost and Units side by side. Repeat a source field with sum, numeric count or average. Every column keeps its own meaning and unit; there is no total across the different Values columns. The row axis and Region/Product filters above select the records.
Why the total changes
For the original uneven source, East / Kit has 1,000 revenue and 180 profit. Its profit rate is 180 ÷ 1,000 = 0.18. Averaging the two source-row rates would give 0.50. The grand rate uses all included revenue and cost again; it is not the sum or mean of displayed rates.
Source sums and expressions use ordinary numeric calculations. Tiny floating-point residuals can affect division and comparisons. Use an explicit ROUND when your business rule needs rounding, then check the complete formulas. An empty intersection still evaluates from zero sums, so a ratio can return a typed division error.
Microsoft: calculated fields and totals ↗