John Serra
Back to Blog
AnalyticsBusiness OperationsData AuditData QualityreportingSmall Business AnalyticsSpreadsheet Managementspreadsheets

When You Should (and Should Not) Average a 1-to-5 Customer Rating

Only average a 1-to-5 customer rating when you state the equal-step assumption upfront and pair the mean with the median and 1-to-2 / 4-to-5 score shares. Walk through a 15-response branch rank flip and 30 portable audit formulas in Google Sheets and Excel for the web.

Only average a 1-to-5 customer rating when you state upfront that you are treating the steps between scores as equal, and report the median plus the 1-to-2 and 4-to-5 score shares alongside that average. Never average numbers that only label categories like postal zones or unordered outcome codes because changing those arbitrary code digits moves the average even when customer results stay the same.

In the 15-response feedback export below, each of the three branches has 5 completed surveys. South averages 3.40 (17 divided by 5) ahead of North at 3.20 (16 divided by 5), while the median flips their order to 3.00 for South versus 4.00 for North. Two 1-star ratings from cancelled parts backorders pull North’s mean down even though 60% of North’s responses (3 of 5) are 4 or 5, whereas West leads on both measures with a 4.60 mean and a 5.00 median. Five responses per branch describe only this small batch of responses, not a permanent ranking of branch performance.

This is the second companion article in Grain First, my open-core data analytics curriculum, following Why Counting Rows Can Mislead You: Row Grain vs. Unit of Analysis. You can try the live Level 1 and Level 2 interactive practice exercises for this unit on CareerTalkLab while the walkthrough video is still being recorded, and business owners checking their own reporting can start with the 4-Minute Small Business Data Audit.

I’ve seen business numbers that should agree fall out of sync across software systems plenty of times. My preferred tool is Google Sheets because a spreadsheet should be shareable. It should make collaborating with others easy. Every formula in this walkthrough returns the same results in Google Sheets and Excel for the web with US English settings.

Below is the 15-row customer feedback export I made up for this article.

Row response_id (A) branch (B) response_date (C) postal_district (D) outcome_code (E) rating_1_to_5 (F) invoice_usd (G)
2 RSP-01 North 2026-10-01 410 1 5 200.00
3 RSP-02 North 2026-10-02 420 4 1 0.00
4 RSP-03 North 2026-10-03 435 2 4 180.00
5 RSP-04 North 2026-10-04 455 1 5 220.00
6 RSP-05 North 2026-10-05 480 4 1 0.00
7 RSP-06 South 2026-10-01 410 1 3 110.00
8 RSP-07 South 2026-10-02 420 2 4 120.00
9 RSP-08 South 2026-10-03 435 3 3 100.00
10 RSP-09 South 2026-10-04 455 1 3 110.00
11 RSP-10 South 2026-10-05 480 3 4 110.00
12 RSP-11 West 2026-10-01 410 1 5 130.00
13 RSP-12 West 2026-10-02 420 2 4 120.00
14 RSP-13 West 2026-10-03 435 1 5 140.00
15 RSP-14 West 2026-10-04 455 2 4 120.00
16 RSP-15 West 2026-10-05 480 2 5 140.00

In the spreadsheet walkthrough below, these seven columns sit in columns A through G on rows 2 to 16; the Row column above is for reading reference only and is left out when pasting. One row records one completed post-service feedback survey, with a unique identifier in response_id. The postal_district column uses fictional 3-digit zones (410, 420, 435, 455, and 480) rather than real US ZIP codes. The outcome_code column assigns digits 1 to 4 to unordered outcomes: 1 means completed in-store (6 rows, 40.0%), 2 means completed by mobile van (5 rows, 33.3%), 3 means referred to a partner shop (2 rows, 13.3%), and 4 means a cancelled parts backorder with $0.00 billed (2 rows, 13.3%). Ratings run from 1 (Very Dissatisfied) to 5 (Very Satisfied), where a blank cell means missing rather than zero, and invoice_usd tracks billed revenue in US dollars.

Here are the branch rollups computed from those 15 rows (shares appear as two-decimal percentages alongside four-decimal proportions, and ratings appear to two decimals):

Branch Responses Bottom-2-Box (1–2) Top-2-Box (4–5) rating_mean rating_median Mean Rank Median Rank What the Numbers Show
North 5 40.00% (0.4000) 60.00% (0.6000) 3.20 4.00 3 2 Polarized: two 1-star cancelled backorders ($0.00 billed) vs. three 4-to-5 visits
South 5 0.00% (0.0000) 40.00% (0.4000) 3.40 3.00 2 3 Lukewarm: zero 1-to-2 ratings, three 3-star visits, and two 4-star visits
West 5 0.00% (0.0000) 100.00% (1.0000) 4.60 5.00 1 1 Aligned: 100.00% 4-to-5 ratings, no 1-to-2 scores, rank 1 on both mean and median
All_Branches 15 13.33% (0.1333) 66.67% (0.6667) 3.73 4.00 N/A N/A Rank flip present across 2 of 3 branches

The rightmost column above summarizes each row in plain English; Step 2 builds the worksheet’s automated scale_guidance flags in column S.

The Two Opposite Rating-Scale Mistakes

Picture a hypothetical weekly operations meeting where one person drops this export into a pivot summary and another refuses to let anyone summarize ratings with an average. Both habits break weekly reporting in opposite ways.

  1. Credulous averaging (the nominal trap): Running =AVERAGE(E2:E16) on outcome_code returns 2.00 (“Completed by mobile van”), even though code 1 (“Completed in-store”) is the most common outcome at 40.0%. Re-number code 4 (“Parts backorder cancelled”) to 9, and =AVERAGE(E2:E16) jumps from 2.00 to 2.67 without changing a single visit. Running =AVERAGE(D2:D16) on postal_district returns 440.00, and changing zone 480 to 900 pushes that mean to 524.00. An unobserved mean value is not the flaw by itself, since averaging two valid invoices of $100.00 and $150.00 produces a valid $125.00 mean even though neither invoice equals $125.00. What fails on nominal labels is running magnitude-based math (AVERAGE, SUM, or numeric rank comparisons) on arbitrary code digits, whereas running Google Sheets MODE or Excel MODE.SNGL on outcome_code preserves the modal category (code 1 with 6 occurrences) under one-to-one relabeling.
  2. Dogmatic refusal: Banning every 1-to-5 rating average creates the opposite trap. On West’s clean unimodal scores (5, 4, 5, 4, 5), both the 4.60 mean and the 5.00 median place West first, with 100.00% of scores at 4 or 5 and 0.00% at 1 or 2. More practically, two unimodal branches often tie on the median: a branch with ratings [4, 4, 4, 4, 5] (mean 4.20) and a branch with [3, 4, 4, 4, 4] (mean 3.80) both have a median of 4.00. As noted in UCLA OARC’s guide to variable measurement levels and OpenStax Introductory Statistics 2e Section 1.3, ordered survey choices do not guarantee equal psychological steps between 1 and 2 versus 4 and 5. Stating the equal-step assumption when you report a rating mean is a practical reporting convention, not proof of equal psychological distance, and pairing that mean with the median and low/high score shares stops polarized scores like North from getting hidden.

Why Odd-Count and Even-Count Medians Behave Differently

With an odd number of ratings per branch (5 rows per branch and 15 rows overall here), Google Sheets MEDIAN and Microsoft Excel MEDIAN return one observed response at the center of the sorted list. Because no averaging occurs between adjacent scores on odd counts, any strictly increasing relabeling preserves the middle category and the ordering between branches.

When a branch has an even number of responses and its two center positions differ, standard spreadsheet MEDIAN returns their arithmetic midpoint. More generally, Google Sheets PERCENTILE and Excel PERCENTILE.INC apply numeric interpolation at fractional rank positions, which can interpolate even on odd sample counts whenever the requested percentile falls between two observations (Google’s notes specifically single out the 50th percentile as equivalent to MEDIAN).

Under strictly increasing nonlinear relabelings, preserving ties or rankings on interpolated even-count medians is not guaranteed. Suppose Branch A has 6 ratings [1, 1, 1, 5, 5, 5] and Branch B has [3, 3, 3, 3, 3, 3]. Spreadsheet MEDIAN returns 3.00 for both, even though nobody in Branch A picked 3 (its two middle categories are 1 and 5). If you square every score (f(x) = x^2), Branch B keeps a median of 9.00, while Branch A’s midpoint jumps to 13.00 ((1 + 25) / 2). Keep conventional spreadsheet MEDIAN for cross-team compatibility, and on even sample sizes pair it with 1-to-2 and 4-to-5 score shares or list the two middle categories whenever they differ.

Classify Every Column Before You Summarize It

Before calculating a single summary, map every column to its measurement scale, just as you would when building a simple data dictionary (Build a Small Data Dictionary for Your Spreadsheet). Spreadsheet formulas happily add or average any cell containing digits, whether those digits represent dollars, calendar serials, or branch codes.

Scale What Numbers Preserve Valid Summaries What Breaks Columns in Export
Nominal Identity and category membership only Counts, % shares, MODE / MODE.SNGL AVERAGE, SUM, magnitude rank response_id, branch, postal_district, outcome_code
Ordinal Rank order without guaranteed equal steps Counts, Bottom-2-Box, Top-2-Box, MEDIAN (note midpoint on even counts), equal-step-disclosed mean SUM, unconditional mean without shares/assumption, assuming ties survive nonlinear recodes on interpolated even-count medians rating_1_to_5
Interval Equal spacing without a true zero MIN, MAX, span (MAX - MIN), AVERAGE (mean date) SUM (694,470 date serials), ratio claims ("twice as recent") response_date
Ratio Equal spacing plus a true zero ($0.00 = none) SUM ($1,800.00), AVERAGE ($120.00), MEDIAN ($120.00), ratios Nothing from scale rules alone (still inspect skew) invoice_usd

Three classic spreadsheet traps show why column classification saves hours of cleanup later:

  • Dates look numeric inside spreadsheet storage: In Google Sheets and Excel, 2026-10-01 is stored as serial number 46296. Running =SUM(C2:C16) returns 694,470, which measures nothing because dates have no true zero point. Summarize response_date with MIN (2026-10-01), MAX (2026-10-05), and the 4-day span. By contrast, invoice_usd is a true ratio variable ($0.00 literally means zero dollars billed on a cancelled backorder), so $1,800.00 total revenue and $120.00 mean revenue per response require zero extra assumptions.
  • Typing zero for a missing rating corrupts the denominator: Both Excel AVERAGE and Google Sheets AVERAGE skip blank cells while including zeros in the divisor, which I cover in Blank or Zero: Why Missing Values Change Your Average. Adding two non-responses coded as 0 drags this dataset’s overall rating mean from 3.73 (56 ÷ 15) down to 3.29 (56 ÷ 17), a 0.44-point drop caused by a score that does not exist on the form.
  • Equal branch counts hide weighting rules: Each branch here happens to have 5 responses, so averaging the three branch means (3.20, 3.40, 4.60) gives the same 3.73 as dividing total stars (56) by total responses (15). Once branch volumes differ, averaging branch averages weights small and large branches equally, the same denominator trap explained in Why You Should Not Average Margin Percentages. Always divide total rating points by total responses across the raw rows, or weight each branch mean by its response count, as the audit sheet does below.

Good metrics should drive operational decisions that protect revenue, improve efficiency, or cut cost, and on small slices they show what to check first rather than proving a cause. Debating whether South’s 3.40 beats North’s 3.20 misses what the score shares surface in these five-response batches: North’s three completed visits scored 4 or 5 ($180.00 to $220.00 billed), while its two 1-star responses were both cancelled parts backorders billed at $0.00 (40.00% low-score share). Five responses per branch cannot establish a branch-wide cause or prescribe a fix, so treat both patterns as investigation leads: check parts backorder records at North, and review the visit notes behind South’s three 3-star responses before reading more into the branch averages.

Build the Side-by-Side Rating Audit in Google Sheets and Excel

This single-sheet layout (Feedback) keeps the raw survey extract in A1:I16, the branch summary and rank-flip check in K1:S5, and the verified benchmark plus candidate report audit in U1:W15, separated by blank spacer columns J and T. Because K1:S5 ends on row 5 and U1:W15 ends on row 15, resizing the raw table never collides with neighboring audit cells.

Before You Paste: Input Rules and Real-Export Mapping

Follow these rules before dropping a real export into A1:G16 so your validation column stays green:

  1. Name the worksheet Feedback, paste only columns A:G, and leave out the preview’s Row column.
  2. Re-key platform survey IDs into RSP-01 through RSP-999 ("RSP-" followed by two or three digits, uppercase, with no spaces, non-breaking spaces, line breaks, or * ? ~ = < >) and keep a lookup crosswalk back to your raw system IDs (Map Inconsistent Customer Names to Stable IDs in Excel and Google Sheets). Row_Check (H2) flags those specific issues; it won’t catch every hidden character.
  3. Map your own branch names to three distinct labels in both B2:B16 and K2:K4, keeping a side crosswalk if your source names contain spaces (for example, mapping "North Shop" to North_Shop in both column B and K2:K4). Every branch label in column B and K2:K4 must match case-sensitively, have at least one valid response (L2:L4 >= 1), and contain no spaces (even when column B and K2:K4 match), non-breaking spaces, line breaks, or * ? ~ = < > because Row_Check (H2) rejects all of those characters. This worksheet layout compares exactly three populated branches ($K$2:$K$4, rows 2 to 4, with All_Branches on row 5); comparing two branches or four or more branches requires rebuilding the branch summary table, which this walkthrough does not cover.
  4. Enter response_date (C2:C16) and the window dates (V2:V3, W2:W3) as whole numeric spreadsheet dates (no timestamps) between 2000-01-01 and 2100-01-01, with every response falling inside V2 through V3. If you import an .xlsx file into Google Sheets, check File > Settings to confirm US English locale (United States) and your expected timezone.
  5. Keep postal_district as an integer from 100 to 99999, outcome_code as an integer from 1 to 4, rating_1_to_5 as an integer from 1 to 5, and invoice_usd zero or higher with no more than two decimal places.

Step 1: Load the Raw Export, Static Controls, and Row Checks

Copy this tab-separated dataset and paste it starting at Feedback!A1 so A1:G16 is populated:

response_id	branch	response_date	postal_district	outcome_code	rating_1_to_5	invoice_usd
RSP-01	North	2026-10-01	410	1	5	200.00
RSP-02	North	2026-10-02	420	4	1	0.00
RSP-03	North	2026-10-03	435	2	4	180.00
RSP-04	North	2026-10-04	455	1	5	220.00
RSP-05	North	2026-10-05	480	4	1	0.00
RSP-06	South	2026-10-01	410	1	3	110.00
RSP-07	South	2026-10-02	420	2	4	120.00
RSP-08	South	2026-10-03	435	3	3	100.00
RSP-09	South	2026-10-04	455	1	3	110.00
RSP-10	South	2026-10-05	480	3	4	110.00
RSP-11	West	2026-10-01	410	1	5	130.00
RSP-12	West	2026-10-02	420	2	4	120.00
RSP-13	West	2026-10-03	435	1	5	140.00
RSP-14	West	2026-10-04	455	2	4	120.00
RSP-15	West	2026-10-05	480	2	5	140.00

Enter all static headers and starting control inputs before pasting live formulas so every check calculates cleanly on first pass:

  • Helper headers (H1:I1): Enter Row_Check in H1 and Response_ID_Freq in I1.
  • Branch table frame (K1:S1 and K2:K5): Put branch, responses, bottom_2_box_share, top_2_box_share, rating_mean, rating_median, mean_rank, median_rank, and scale_guidance across K1:S1, then enter North, South, West, and All_Branches in K2:K5 (or enter your own three distinct, space-free branch labels in K2:K4 plus All_Branches in K5 when loading your own three-branch data).
  • Audit table frame (U1:W1, U2:U15): Put Check_Item, Verified_Sheet_Benchmark, and Candidate_Summary_Audit across U1:W1, then enter these exact row labels down U2:U15: Window_Start_Date, Window_End_Date, Audited_Column, Measurement_Scale, Proposed_Summary, Equal_Step_Assumption_Stated, Invalid_Or_Trailing_Cell_Count, Valid_Response_Count, Total_Invoice_USD, Overall_Rating_Mean, Overall_Rating_Median, Rank_Flip_Branch_Count, Reconciliation_Gap, and Audit_Verdict.
  • Static benchmark controls (V2:V4, V7): Enter =DATE(2026,10,1) in V2 and =DATE(2026,10,5) in V3 (formatted yyyy-mm-dd), enter rating_1_to_5 in V4, and enter NO in V7.
  • Starting candidate cells (W2:W13): Enter =DATE(2026,10,1) in W2 and =DATE(2026,10,5) in W3 (formatted yyyy-mm-dd), 0 in W8, and NOT TESTED in W4:W7 and W9:W13.

Now paste the H2 validation formula from the reference section into H2 and fill down through H16, then paste I2 and fill down through I16. All 15 rows return "OK" in H2:H16, and EXACT (Google Sheets, Excel) inside SUMPRODUCT (Google Sheets, Excel) returns 1 down I2:I16. Anytime any row in H2:H16 is not "OK", I2:I16 intentionally stay blank ("") so bad rows cannot quietly leak into branch rollups.

Google Sheets view of Feedback!A1:I16 showing the 15 baseline survey rows RSP-01 through RSP-15 across columns A to G alongside Row_Check OK in H2:H16 and Response_ID_Freq 1 in I2:I16.
In Google Sheets, Feedback!A1:G16 holds the 15 baseline survey responses while H2:I16 confirm every row passes formatting checks with unique response IDs. Open full-size image.

Step 2: Build the Branch Comparison and Rank-Flip Table

Paste each formula from L2 through S2 in the reference section into cells L2:S2 of the branch table and fill L2:S2 down through row 4 (North, South, West), then paste the corresponding formulas into L5:S5 (All_Branches):

  • Shares, mean, and cumulative median (M2:P5): Format M2:N5 as 0.0000 or 0.00% and O2:P5 as 0.00, keeping the underlying formulas unrounded so larger exports do not fail exact integer count checks. P2:P4 count how many ratings for each branch fall at or below 1, 2, 3, and 4 using INT((L2+1)/2) and INT((L2+2)/2). That avoids legacy Ctrl+Shift+Enter array formulas (MEDIAN(IF(...))) while matching standard spreadsheet MEDIAN on both odd counts (4.00, 3.00, 5.00) and even counts (middle-two midpoint).
  • Full-precision ranking (Q2:R4 and S2:S5): Q2 ranks unrounded means (O2:O4), while R2 ranks medians (P2:P4). Keeping M2:P5 unrounded prevents Q2 from creating false 0.01-rounded ties between different branch averages such as 23 ÷ 7 (3.2857…) and 56 ÷ 17 (3.2941…) that both display as 3.29, while exact rational ties still receive the same top competition rank. S2 marks a branch as "RANK FLIP: REPORT MEDIAN & SHARES" when its mean rank and median rank disagree (Q2<>R2, as on North and South), as "POLARIZED: PAIR MEAN WITH SHARES" when mean and median ranks agree while the branch has both 1-to-2 and 4-to-5 scores (M2>0 and N2>0), and as "ALIGNED: MEAN OK IF EQUAL-STEP STATED" when ranks agree without both low-end and high-end pockets (as on West). S5 summarizes the sheet as "BRANCH RANK FLIP PRESENT (2 OF 3)".
Google Sheets view of Feedback!K1:S5 comparing North, South, West, and All_Branches, showing South ahead of North on mean (3.40 vs. 3.20, ranks 2 vs. 3) while North leads South on median (4.00 vs. 3.00, ranks 2 vs. 3), West ranked 1 on both as ALIGNED: MEAN OK IF EQUAL-STEP STATED, and S5 reading BRANCH RANK FLIP PRESENT (2 OF 3).
In Google Sheets, Feedback!K1:S5 surfaces the North-vs-South mean/median rank flip while confirming West holds rank 1 on both measures. Open full-size image.

Step 3: Reconcile Benchmarks and Audit Proposed Summaries

Paste the matching formulas from the Copy-Paste Formula Reference into V5:V6, V8:V15, and W14:W15 on Feedback:

  • Whole-column tail guard (V8): =COUNTIF($H$2:$H$16, "<>OK")+COUNTBLANK($A$2:$G$16)+(COUNTA($A:$G)-COUNTA($A$1:$G$16)) checks row validation failures, blank input cells, and any stray values or formula-returned empty strings left below row 16 anywhere in columns A:G. Always keep temporary scratch check formulas outside A:G (such as column Y), and delete them when done.
  • Exact source rollup vs. rounded candidate controls (V11:V14): V11 (3.73) and V12 (4.00) apply ROUND(..., 2) so hand-typed numbers from rounded charts or decks (W11:W12) reconcile cleanly, separate from full-precision O5 (56 ÷ 15 = 3.7333…) and P5. Meanwhile, V14 reconciles branch SUMPRODUCT totals (L2:L4 times M2:M4, N2:N4, O2:O4) and overall rollup L5 products directly against integer counts and exact rating sums within ROUND(..., 8), so scaling to larger tables (such as a 160-row export where 1 ÷ 150 displays as 0.0067) never triggers false rounding gaps. On clean baseline data, V14 returns 0 and V15 returns "RECONCILED: 15 RESPONSES ACROSS 3 BRANCHES ($1800.00 INVOICES; 2 RANK-FLIP BRANCHES)".

To audit a proposed chart, table, or headline summary on your own report, enter its metadata in W4:W7 and enter the candidate report’s claimed control figures in W9:W13 to compare against V9:V13 (keeping W2 = 2026-10-01, W3 = 2026-10-05, and W8 = 0 matching the verified source). In Scenarios 1 through 8 below, W9:W13 hold the same five dataset-wide baseline figures from V9:V13 (valid response count, total invoice dollars, overall rating mean, overall rating median, and rank-flip branch count) so each row isolates a scale or method choice in W4:W7, while Scenario 9 alters W9 and W11 to test a corrupted benchmark claim:

Scenario W4 (Audited_Column) W5 (Measurement_Scale) W6 (Proposed_Summary) W7 (Equal_Step) W9 / W10 / W11 / W12 / W13 W14 (Gap) W15 (Audit_Verdict)
Scenario 0 NOT TESTED NOT TESTED NOT TESTED NOT TESTED All NOT TESTED NOT TESTED NOT TESTED: ENTER CANDIDATE SUMMARY AUDIT
Scenario 1 postal_district RATIO MEAN_UNCONDITIONAL NO 15 / 1800.00 / 3.73 / 4.00 / 2 0.0000 STOP: WRONG MEASUREMENT SCALE (postal_district IS NOMINAL, NOT RATIO)
Scenario 2 outcome_code NOMINAL MEAN_UNCONDITIONAL NO 15 / 1800.00 / 3.73 / 4.00 / 2 0.0000 STOP: ILLEGITIMATE SUMMARY FOR NOMINAL SCALE (NO ORDER OR EQUAL STEPS; USE MODE/COUNTS)
Scenario 3 rating_1_to_5 ORDINAL MEAN_UNCONDITIONAL NO 15 / 1800.00 / 3.73 / 4.00 / 2 0.0000 STOP: UNCONDITIONAL ORDINAL MEAN (STATE EQUAL-STEP ASSUMPTION & PAIR MEDIAN/SHARES)
Scenario 4 rating_1_to_5 ORDINAL MEAN_EQUAL_STEP_CHECKED NO 15 / 1800.00 / 3.73 / 4.00 / 2 0.0000 STOP: ORDINAL MEAN REQUIRES EXPLICIT EQUAL-STEP ASSUMPTION (SET W7=YES)
Scenario 5 rating_1_to_5 ORDINAL MEAN_EQUAL_STEP_CHECKED YES 15 / 1800.00 / 3.73 / 4.00 / 2 0.0000 DECLARED METHOD & CONTROLS CHECKED: VERIFY PROSE ASSUMPTION & DISPLAYED SHARES
Scenario 6 rating_1_to_5 ORDINAL MEDIAN_AND_SHARES NO 15 / 1800.00 / 3.73 / 4.00 / 2 0.0000 RECONCILED: DECLARED ORDINAL METHOD & BENCHMARK CONTROLS MATCH (VERIFY REPORT OUTPUT)
Scenario 7 invoice_usd RATIO MEAN_UNCONDITIONAL NO 15 / 1800.00 / 3.73 / 4.00 / 2 0.0000 RECONCILED: DECLARED RATIO METHOD & BENCHMARK CONTROLS MATCH (VERIFY REPORT OUTPUT)
Scenario 8 response_date INTERVAL SUM_TOTAL NO 15 / 1800.00 / 3.73 / 4.00 / 2 0.0000 STOP: ILLEGITIMATE SUMMARY FOR INTERVAL SCALE (SUM REQUIRES TRUE ZERO; USE MIN/MAX SPAN)
Scenario 9 rating_1_to_5 ORDINAL MEDIAN_AND_SHARES NO 17 / 1800.00 / 3.29 / 4.00 / 2 2.4400 STOP: SUPPORTING BENCHMARK MISMATCH (CHECK COUNTS, RATINGS, OR RANK FLIPS)
Google Sheets view of Feedback!U1:W15 under Scenario 3 where Verified_Sheet_Benchmark reconciles cleanly while Candidate_Summary_Audit sets W4 to rating_1_to_5, W5 to ORDINAL, W6 to MEAN_UNCONDITIONAL, and W7 to NO, triggering STOP: UNCONDITIONAL ORDINAL MEAN (ADD MEDIAN, BOX SHARES, AND EQUAL-STEP ASSUMPTION) in W15.
In Google Sheets, testing an unconditional rating mean (W6 = MEAN_UNCONDITIONAL, W7 = NO) in Scenario 3 leaves column V reconciled while W15 stops candidate reporting. Open full-size image.

Notice the hard boundary on W14:W15: these formulas verify source health, populated-branch coverage, rollup math, scale classification (W5), declared summary method (W6), the W7 (YES/NO) flag, and the five supporting benchmark controls (W9:W13). They cannot inspect narrative slides to prove that the reader’s prose actually states the equal-step assumption, displays Bottom-2-Box / Top-2-Box share tiles, or quotes the right modal outcome label, date span, or dollar average. That is why Scenario 5 returns "DECLARED METHOD & CONTROLS CHECKED: VERIFY PROSE ASSUMPTION & DISPLAYED SHARES" and Scenarios 6 and 7 instruct you to verify the report output manually.

Google Sheets view of Feedback!U1:W15 under Scenario 5 showing Verified_Sheet_Benchmark reconciled (15 responses, $1,800.00 invoices, 2 rank-flip branches), W14 at 0.0000, and Candidate_Summary_Audit returning DECLARED METHOD & CONTROLS CHECKED: VERIFY PROSE ASSUMPTION & DISPLAYED SHARES in W15.
In Google Sheets, Scenario 5 reconciles all five numeric controls in W9:W13 with W14 = 0.0000 while reminding reviewers that W15 still requires checking prose assumptions and displayed score shares. Open full-size image.

Step 4: Test Five Real-World Failure and Boundary Modes (and Reset Cleanly)

Run these five checks on a copy of Feedback (or restore each cell immediately) to verify that the guards stop bad reporting without silent failures:

  1. Text-date trap ('2026-10-01 in C2) and Google Sheets re-parsing difference: Type '2026-10-01 with a leading apostrophe into C2, then enter =ISNUMBER(C2) in a temporary scratch cell outside A:G such as Y2 (FALSE). H2 switches to "CHECK ROW", I2:I16 blank out, L2:S5 display "CHECK INPUTS", V8 becomes 1, V15 stops with "STOP: CHECK SOURCE OR TRAILING ROWS", and W15 stops with "STOP: INVALID, BLANK, OR TRAILING SOURCE ROWS". In Google Sheets (en_US), applying Format > Number > Date directly on C2 automatically re-parses the text string into numeric 10/1/2026 (H2 = "OK"), whereas Excel for the web keeps '2026-10-01 as text until you re-enter a true date or convert it. Reset C2 to =DATE(2026,10,1) formatted yyyy-mm-dd, clear Y2, and confirm V8 = 0 and V15 returns "RECONCILED: 15 RESPONSES ACROSS 3 BRANCHES ($1800.00 INVOICES; 2 RANK-FLIP BRANCHES)".
  2. Duplicate ID (A3 = "RSP-01") and zero rating (F3 = 0): Change A3 alone to RSP-01 (H2:H16 stay "OK", while I2 and I3 both change to 2; if you also changed A16 to RSP-01 at the same time, the count would be 3). L2:S5, V13, V14, and W14 return "CHECK INPUTS", and both V15 and W15 return "STOP: DUPLICATE RESPONSE IDS IN SOURCE". Restore A3 = "RSP-02". Next, change F3 to 0 (simulating a missed score entered as zero): H3 shows "CHECK ROW", V8 becomes 1, and V15 and W15 stop. Restore F3 = 1 and confirm baseline reconciliation returns.
  3. Stray trailing row (A17 = "RSP-16"): Type RSP-16 into A17 without resizing the formulas. V8 increments to 1 via the whole-column COUNTA($A:$G) check, turning V9 and L2:S5 to "CHECK INPUTS" and stopping both V15 and W15. Clear A17 and confirm V8 = 0.
  4. Empty branch coverage stop (B2:B6 = "South"): Enter Scenario 5 values into W4:W13 (W4 = rating_1_to_5, W5 = ORDINAL, W6 = MEAN_EQUAL_STEP_CHECKED, W7 = YES, W8 = 0, W9 = 15, W10 = 1800.00, W11 = 3.73, W12 = 4.00), change B2:B6 to South so North has zero rows, and set W13 = 0 (to test whether an empty-branch sheet can pass as zero rank flips). Live spreadsheet inspection confirms that not every cell in M2:S5 turns to text errors: North displays L2 = 0 and M2:S2 = "CHECK INPUTS", populated South (L3 = 10, M3 = 0.2000, N3 = 0.5000, O3 = 3.30, P3 = 3.50), West, and All_Branches (O5 = 3.73 from 56 ÷ 15, P5 = 4.00) keep numeric descriptive values, while all comparative ranks and guidance (Q2:S5) plus V13, V14, and W14 show "CHECK INPUTS", and both V15 and W15 return "STOP: EMPTY OR INCOMPLETE BRANCH COVERAGE". Restore B2:B6 to North and reset W4:W7, W9:W13 to NOT TESTED so V15 and W15 return to baseline.
  5. Genuine zero-flip branch alignment (F2:F6 = 2, 2, 2, 3, 1): Keep all three branches populated and set F2:F6 to 2, 2, 2, 3, 1 (North mean 2.00, median 2.00; overall mean 3.33, median 3.00), then enter Scenario 5’s equal-step metadata in W4:W13 (W4 = rating_1_to_5, W5 = ORDINAL, W6 = MEAN_EQUAL_STEP_CHECKED, W7 = YES, W8 = 0, W9 = 15, W10 = 1800.00, W11 = 3.33, W12 = 3.00, W13 = 0). Now S5 displays "BRANCH RANKS ALIGNED", V13 = 0, V15 reconciles with 0 RANK-FLIP BRANCHES, W14 displays 0.0000, and W15 returns "DECLARED METHOD & CONTROLS CHECKED: VERIFY PROSE ASSUMPTION & DISPLAYED SHARES". Restore F2:F6 to 5, 1, 4, 5, 1 and W4:W7, W9:W13 to NOT TESTED, confirming V15 returns to "RECONCILED: 15 RESPONSES ACROSS 3 BRANCHES ($1800.00 INVOICES; 2 RANK-FLIP BRANCHES)" and W15 returns to "NOT TESTED: ENTER CANDIDATE SUMMARY AUDIT".
Excel for the Web view of K1:W15 when North rows B2:B6 are changed to South, showing North L2 at 0 with CHECK INPUTS in M2:S2, South L3 at 10 with numeric descriptive rollups, All_Branches with O5 at 3.73 and P5 at 4.00, CHECK INPUTS across Q2:S5, V13, V14, and W14, and STOP: EMPTY OR INCOMPLETE BRANCH COVERAGE in V15 and W15.
In Excel for the web, reassigning North rows to South drops North count L2 to 0, keeps populated descriptive rollups intact, and halts ranks, rollups, and audit verdicts at V15 and W15. Open full-size image.

Step 5: Resize Safely From a Fresh 15-Row Baseline Copy

This procedure resizes only the response row count while keeping the three-branch table (K2:K4 and All_Branches in K5) unchanged. Always start each new three-branch export of N responses (N >= 3, with at least one response for each listed branch, ending at row R = N + 1) from an untouched master copy of the 15-row baseline Feedback sheet (A1:W16):

  1. On a fresh copy of the 15-row baseline, paste your N responses into A2:G<R> (formatted consistently as numeric dates and numbers) mapped to three distinct space-free branch names matching K2:K4. Do not delete or insert entire worksheet rows across K:W.
  2. Select H2:W16 (covering all 74 baseline formula cells across rows 2 to 16) and open the Find and Replace option. In Google Sheets (Edit > Find and replace), check Also search within formulas (leaving it unchecked matches 0 formula references). In Excel for the web (Home > Find & Select > Replace), formulas update directly without a separate formula checkbox.
  3. Replace only the six column-qualified source end tokens in H2:W16: replace :$A$16 (36 baseline sheet occurrences), :$B$16 (36), :$F$16 (43), :$G$16 (3), :$H$16 (16), and :$I$16 (11) with :$A$<R>, :$B$<R>, :$F$<R>, :$G$<R>, :$H$<R>, and :$I$<R> (145 total formula-cell occurrences across the 15-row baseline H2:W16; columns C, D, and E have zero row-16 end bounds, and even shrinking to 9 rows at row 10 updates :$A$10 through :$I$10 without touching $V$10 or $U$15). Never replace a bare 16 or 10.
  4. If N > 15, fill H2:I2 down through H<R>:I<R>. If 3 <= N < 15, clear the unused baseline cells A<R+1>:I16 after step 3 (because you started from a fresh 15-row baseline copy, no prior rows exist below row 16).

What to Say in the Room

In a stakeholder review, technical scale names sound defensive. Translate the distribution directly into what changed and what action comes next:

  • Don’t say: “Ordinal rating means fail strict interval assumptions across Likert scales, and North drops from Rank 2 to Rank 3 on an uncalibrated parametric mean while outcome_code is nominal.”
  • Do say: “Each branch only has 5 responses in this batch, so treat these numbers as leads to check rather than a branch ranking. West leads on both average (4.60) and middle score (5.00), with 100% of its responses at 4 or 5. North averages lower than South (3.20 vs. 3.40) because two 1-star responses pull its mean down, even though North’s middle score is 4.00 and 60% of North’s responses are 4 or 5. Those two 1-star rows were both cancelled backorders at $0.00 billed, so the first follow-up is checking parts availability at North and inspecting the notes behind South’s 60% 3-star responses.”

Frequently Asked Questions

Can you average branch rating averages when branches have different response counts?

No. If you report an overall rating average alongside the equal-step assumption, average the raw responses column (F2:F16 via O5) or compute a count-weighted average using =SUMPRODUCT(L2:L4, O2:O4)/L5. An unweighted average of branch averages gives a 5-response branch the same weight as a 150-response branch.

Why show both 1-to-2 and 4-to-5 shares instead of subtracting low scores from high scores?

A single net score collapses distinct customer experiences into identical numbers. For example, 40% top-box minus 20% bottom-box nets to +20 percentage points, and 20% top-box minus 0% bottom-box also nets to +20 percentage points, hiding the 20% dissatisfied pocket that needs operational attention.

Does switching from a 1-to-5 scale to a 1-to-7 or 1-to-10 scale make the average exact?

Wider integer scales can offer finer separation, though they do not guarantee fewer median ties in every dataset, and the numbers remain ordered categories unless step distances are formally calibrated. Conceptually, the same reporting rule applies on 1-to-7 or 1-to-10 forms: state the equal-step assumption upfront, and pair any rating average with the median and low/high score shares. Note that this workbook’s formulas (H2 validation and P2‘s cumulative median) only support integer 1-to-5 ratings, so applying the sheet to a 1-to-7 or 1-to-10 scale requires adapting and retesting the model.

Copy-Paste Formula Reference

Enter all static headers, dates, and control values from Step 1 first, then paste these 30 exact formulas into the Feedback worksheet (all quotes use standard straight ASCII quotation marks):

H2, Row validation guard (enter in H2, fill down H2:H16):

=IF(OR(COUNTA(A2:G2)<7, NOT(ISTEXT(A2)), NOT(ISTEXT(B2)), NOT(ISNUMBER(C2)), NOT(ISNUMBER(D2)), NOT(ISNUMBER(E2)), NOT(ISNUMBER(F2)), NOT(ISNUMBER(G2)), NOT(ISNUMBER($V$2)), NOT(ISNUMBER($V$3))), "CHECK ROW", IF(OR(TRIM(SUBSTITUTE(A2, CHAR(160), " "))="", TRIM(SUBSTITUTE(B2, CHAR(160), " "))="", NOT(EXACT(A2, TRIM(SUBSTITUTE(A2, CHAR(160), " ")))), NOT(EXACT(B2, TRIM(SUBSTITUTE(B2, CHAR(160), " ")))), NOT(ISERROR(FIND(" ", A2&B2))), NOT(ISERROR(FIND(CHAR(10), A2&B2))), NOT(ISERROR(FIND(CHAR(13), A2&B2))), NOT(ISERROR(FIND("*", A2&B2))), NOT(ISERROR(FIND("?", A2&B2))), NOT(ISERROR(FIND("~", A2&B2))), NOT(ISERROR(FIND("=", A2&B2))), NOT(ISERROR(FIND("<", A2&B2))), NOT(ISERROR(FIND(">", A2&B2))), LEN(A2)<6, LEN(A2)>7, NOT(EXACT(LEFT(A2, 4), "RSP-")), NOT(ISNUMBER(FIND(MID(A2, 5, 1), "0123456789"))), NOT(ISNUMBER(FIND(MID(A2, 6, 1), "0123456789"))), IF(LEN(A2)=7, NOT(ISNUMBER(FIND(MID(A2, 7, 1), "0123456789"))), FALSE), SUMPRODUCT(--EXACT($K$2:$K$4, B2))<>1, INT($V$2)<>$V$2, INT($V$3)<>$V$3, $V$2<DATE(2000,1,1), $V$3>DATE(2100,1,1), $V$3<$V$2, INT(C2)<>C2, C2<$V$2, C2>$V$3, INT(D2)<>D2, D2<100, D2>99999, INT(E2)<>E2, E2<1, E2>4, INT(F2)<>F2, F2<1, F2>5, G2<0, ROUND(G2, 2)<>G2), "CHECK ROW", "OK"))

I2, Case-sensitive response ID duplicate check (enter in I2, fill down I2:I16):

=IF(OR(NOT(EXACT(H2, "OK")), COUNTIF($H$2:$H$16, "OK")<ROWS($A$2:$A$16)), "", SUMPRODUCT(--EXACT($A$2:$A$16, A2)))

L2, Branch response count (enter in L2, fill down L2:L4):

=IF(OR($V$8>0, COUNT($I$2:$I$16)<ROWS($A$2:$A$16), COUNTIF($I$2:$I$16, ">1")>0, NOT(ISTEXT(K2)), TRIM(SUBSTITUTE(K2, CHAR(160), " "))="", NOT(EXACT(K2, TRIM(SUBSTITUTE(K2, CHAR(160), " ")))), SUMPRODUCT(--EXACT($K$2:$K$4, K2))<>1), "CHECK INPUTS", SUMPRODUCT(--EXACT($B$2:$B$16, K2)))

M2, Branch Bottom-2-Box share, ratings 1 and 2 (enter in M2, fill down M2:M4; keep unrounded, format as 0.0000 or 0.00%):

=IF(OR(NOT(ISNUMBER(L2)), L2<=0), "CHECK INPUTS", SUMPRODUCT(--EXACT($B$2:$B$16, K2), --($F$2:$F$16<=2))/L2)

N2, Branch Top-2-Box share, ratings 4 and 5 (enter in N2, fill down N2:N4; keep unrounded, format as 0.0000 or 0.00%):

=IF(OR(NOT(ISNUMBER(L2)), L2<=0), "CHECK INPUTS", SUMPRODUCT(--EXACT($B$2:$B$16, K2), --($F$2:$F$16>=4))/L2)

O2, Branch rating mean (enter in O2, fill down O2:O4; keep unrounded, format as 0.00):

=IF(OR(NOT(ISNUMBER(L2)), L2<=0), "CHECK INPUTS", SUMPRODUCT(--EXACT($B$2:$B$16, K2), $F$2:$F$16)/L2)

P2, Branch rating median over integers 1 to 5 (enter in P2, fill down P2:P4; keep unrounded, format as 0.00):

=IF(OR(NOT(ISNUMBER(L2)), L2<=0), "CHECK INPUTS", (1+IF(INT((L2+1)/2)>SUMPRODUCT(--EXACT($B$2:$B$16, K2), --($F$2:$F$16<=1)), 1, 0)+IF(INT((L2+1)/2)>SUMPRODUCT(--EXACT($B$2:$B$16, K2), --($F$2:$F$16<=2)), 1, 0)+IF(INT((L2+1)/2)>SUMPRODUCT(--EXACT($B$2:$B$16, K2), --($F$2:$F$16<=3)), 1, 0)+IF(INT((L2+1)/2)>SUMPRODUCT(--EXACT($B$2:$B$16, K2), --($F$2:$F$16<=4)), 1, 0)+1+IF(INT((L2+2)/2)>SUMPRODUCT(--EXACT($B$2:$B$16, K2), --($F$2:$F$16<=1)), 1, 0)+IF(INT((L2+2)/2)>SUMPRODUCT(--EXACT($B$2:$B$16, K2), --($F$2:$F$16<=2)), 1, 0)+IF(INT((L2+2)/2)>SUMPRODUCT(--EXACT($B$2:$B$16, K2), --($F$2:$F$16<=3)), 1, 0)+IF(INT((L2+2)/2)>SUMPRODUCT(--EXACT($B$2:$B$16, K2), --($F$2:$F$16<=4)), 1, 0))/2)

Q2, Full-precision branch mean competition rank (enter in Q2, fill down Q2:Q4):

=IF(OR(NOT(ISNUMBER(O2)), COUNT($O$2:$O$4)<ROWS($K$2:$K$4)), "CHECK INPUTS", 1+SUMPRODUCT(--($O$2:$O$4>O2)))

R2, Full-precision branch median competition rank (enter in R2, fill down R2:R4):

=IF(OR(NOT(ISNUMBER(P2)), COUNT($P$2:$P$4)<ROWS($K$2:$K$4)), "CHECK INPUTS", 1+SUMPRODUCT(--($P$2:$P$4>P2)))

S2, Branch scale guidance flag (enter in S2, fill down S2:S4):

=IF(OR(NOT(ISNUMBER(Q2)), NOT(ISNUMBER(R2)), NOT(ISNUMBER(M2)), NOT(ISNUMBER(N2))), "CHECK INPUTS", IF(Q2<>R2, "RANK FLIP: REPORT MEDIAN & SHARES", IF(AND(M2>0, N2>0), "POLARIZED: PAIR MEAN WITH SHARES", "ALIGNED: MEAN OK IF EQUAL-STEP STATED")))

L5, All_Branches response count rollup (enter in L5):

=IF(OR(NOT(EXACT(K5, "All_Branches")), COUNT($L$2:$L$4)<ROWS($K$2:$K$4)), "CHECK INPUTS", SUM($L$2:$L$4))

M5, All_Branches Bottom-2-Box share (enter in M5; keep unrounded, format as 0.0000 or 0.00%):

=IF(OR(NOT(ISNUMBER(L5)), L5<=0), "CHECK INPUTS", COUNTIF($F$2:$F$16, "<=2")/L5)

N5, All_Branches Top-2-Box share (enter in N5; keep unrounded, format as 0.0000 or 0.00%):

=IF(OR(NOT(ISNUMBER(L5)), L5<=0), "CHECK INPUTS", COUNTIF($F$2:$F$16, ">=4")/L5)

O5, All_Branches overall rating mean (enter in O5; keep unrounded, format as 0.00):

=IF(OR(NOT(ISNUMBER(L5)), L5<=0), "CHECK INPUTS", AVERAGE($F$2:$F$16))

P5, All_Branches overall rating median (enter in P5; keep unrounded, format as 0.00):

=IF(OR(NOT(ISNUMBER(L5)), L5<=0), "CHECK INPUTS", MEDIAN($F$2:$F$16))

Q5, All_Branches mean rank guard (enter in Q5):

=IF(COUNT($Q$2:$Q$4)<ROWS($K$2:$K$4), "CHECK INPUTS", "ALL BRANCHES")

R5, All_Branches median rank guard (enter in R5):

=IF(COUNT($R$2:$R$4)<ROWS($K$2:$K$4), "CHECK INPUTS", "ALL BRANCHES")

S5, All_Branches overall rank-flip summary (enter in S5):

=IF(OR(NOT(ISNUMBER(L5)), COUNT($Q$2:$Q$4)<ROWS($K$2:$K$4), COUNT($R$2:$R$4)<ROWS($K$2:$K$4)), "CHECK INPUTS", IF(COUNTIF($S$2:$S$4, "RANK FLIP*")>0, "BRANCH RANK FLIP PRESENT (" & COUNTIF($S$2:$S$4, "RANK FLIP*") & " OF " & ROWS($K$2:$K$4) & ")", "BRANCH RANKS ALIGNED"))

V5, Active audited column scale lookup (enter in V5):

=IF(NOT(ISTEXT(IF(EXACT(W4, "NOT TESTED"), V4, W4))), "UNKNOWN COLUMN", IF(OR(EXACT(IF(EXACT(W4, "NOT TESTED"), V4, W4), "response_id"), EXACT(IF(EXACT(W4, "NOT TESTED"), V4, W4), "branch"), EXACT(IF(EXACT(W4, "NOT TESTED"), V4, W4), "postal_district"), EXACT(IF(EXACT(W4, "NOT TESTED"), V4, W4), "outcome_code")), "NOMINAL", IF(EXACT(IF(EXACT(W4, "NOT TESTED"), V4, W4), "response_date"), "INTERVAL", IF(EXACT(IF(EXACT(W4, "NOT TESTED"), V4, W4), "rating_1_to_5"), "ORDINAL", IF(EXACT(IF(EXACT(W4, "NOT TESTED"), V4, W4), "invoice_usd"), "RATIO", "UNKNOWN COLUMN")))))

V6, Default legitimate summary method lookup (enter in V6):

=IF(EXACT(V5, "NOMINAL"), "MODE_AND_COUNTS", IF(EXACT(V5, "INTERVAL"), "MIN_MAX_SPAN", IF(EXACT(V5, "ORDINAL"), "MEDIAN_AND_SHARES", IF(EXACT(V5, "RATIO"), "MEAN_UNCONDITIONAL", "CHECK COLUMN"))))

V8, Invalid, blank, and whole-column trailing cell counter across A:G (enter in V8):

=COUNTIF($H$2:$H$16, "<>OK")+COUNTBLANK($A$2:$G$16)+(COUNTA($A:$G)-COUNTA($A$1:$G$16))

V9, Verified valid response count benchmark (enter in V9):

=IF(OR(V8>0, COUNT($I$2:$I$16)<ROWS($A$2:$A$16)), "CHECK INPUTS", COUNTA($A$2:$A$16))

V10, Verified total invoice USD benchmark (enter in V10):

=IF(NOT(ISNUMBER(V9)), "CHECK INPUTS", ROUND(SUM($G$2:$G$16), 2))

V11, Rounded 2-decimal overall rating mean control for W11 checks (enter in V11):

=IF(OR(NOT(ISNUMBER(V9)), NOT(ISNUMBER(O5))), "CHECK INPUTS", ROUND(O5, 2))

V12, Rounded 2-decimal overall rating median control for W12 checks (enter in V12):

=IF(OR(NOT(ISNUMBER(V9)), NOT(ISNUMBER(P5))), "CHECK INPUTS", ROUND(P5, 2))

V13, Verified rank-flip branch count benchmark (enter in V13):

=IF(OR(NOT(ISNUMBER(V9)), NOT(ISNUMBER(L5)), COUNT($L$2:$L$4)<ROWS($K$2:$K$4), COUNTIF($L$2:$L$4, "<=0")>0, COUNT($M$2:$M$4)<ROWS($K$2:$K$4), COUNT($N$2:$N$4)<ROWS($K$2:$K$4), COUNT($O$2:$O$4)<ROWS($K$2:$K$4), COUNT($P$2:$P$4)<ROWS($K$2:$K$4), COUNT($Q$2:$Q$4)<ROWS($K$2:$K$4), COUNT($R$2:$R$4)<ROWS($K$2:$K$4), (COUNTIF($S$2:$S$4, "RANK FLIP*")+COUNTIF($S$2:$S$4, "POLARIZED:*")+COUNTIF($S$2:$S$4, "ALIGNED:*"))<ROWS($K$2:$K$4), IF(ISTEXT(Q5), NOT(EXACT(Q5, "ALL BRANCHES")), TRUE), IF(ISTEXT(R5), NOT(EXACT(R5, "ALL BRANCHES")), TRUE), IF(ISTEXT(S5), EXACT(S5, "CHECK INPUTS"), TRUE)), "CHECK INPUTS", COUNTIF($S$2:$S$4, "RANK FLIP*"))

V14, Exact integer-count and sum reconciliation gap between L2:O5 and source rows (enter in V14):

=IF(OR(NOT(ISNUMBER(V9)), NOT(ISNUMBER(L5)), NOT(ISNUMBER(M5)), NOT(ISNUMBER(N5)), NOT(ISNUMBER(O5)), NOT(ISNUMBER(V10)), NOT(ISNUMBER(V11)), NOT(ISNUMBER(V12)), NOT(ISNUMBER(V13)), COUNT($L$2:$L$4)<ROWS($K$2:$K$4), COUNTIF($L$2:$L$4, "<=0")>0, COUNT($M$2:$M$4)<ROWS($K$2:$K$4), COUNT($N$2:$N$4)<ROWS($K$2:$K$4), COUNT($O$2:$O$4)<ROWS($K$2:$K$4), COUNT($P$2:$P$4)<ROWS($K$2:$K$4)), "CHECK INPUTS", ROUND(ABS(L5-V9)+ABS(V9-ROWS($A$2:$A$16))+COUNTIF($I$2:$I$16, ">1")+ABS(ROUND(SUMPRODUCT($L$2:$L$4, $M$2:$M$4)-COUNTIF($F$2:$F$16, "<=2"), 8))+ABS(ROUND(SUMPRODUCT($L$2:$L$4, $N$2:$N$4)-COUNTIF($F$2:$F$16, ">=4"), 8))+ABS(ROUND(SUMPRODUCT($L$2:$L$4, $O$2:$O$4)-SUM($F$2:$F$16), 8))+ABS(ROUND(L5*M5-COUNTIF($F$2:$F$16, "<=2"), 8))+ABS(ROUND(L5*N5-COUNTIF($F$2:$F$16, ">=4"), 8))+ABS(ROUND(L5*O5-SUM($F$2:$F$16), 8)), 4))

V15, Verified source benchmark audit verdict (enter in V15):

=IF(OR(V8>0, NOT(ISNUMBER(V9))), "STOP: CHECK SOURCE OR TRAILING ROWS", IF(COUNTIF($I$2:$I$16, ">1")>0, "STOP: DUPLICATE RESPONSE IDS IN SOURCE", IF(OR(COUNT($L$2:$L$4)<ROWS($K$2:$K$4), COUNTIF($L$2:$L$4, "<=0")>0, NOT(ISNUMBER(V13))), "STOP: EMPTY OR INCOMPLETE BRANCH COVERAGE", IF(IF(ISNUMBER(V14), V14<>0, TRUE), "STOP: BRANCH ROLLUP RECONCILIATION GAP", "RECONCILED: " & V9 & " RESPONSES ACROSS " & ROWS($K$2:$K$4) & " BRANCHES ($" & TEXT(V10, "0.00") & " INVOICES; " & V13 & " RANK-FLIP BRANCHES)"))))

W14, Candidate summary vs. verified benchmark control gap (enter in W14):

=IF(OR(V8>0, NOT(ISNUMBER(V9)), COUNT($L$2:$L$4)<ROWS($K$2:$K$4), COUNTIF($L$2:$L$4, "<=0")>0, NOT(ISNUMBER(V13)), NOT(ISNUMBER(V14)), COUNTIF($I$2:$I$16, ">1")>0, IF(ISNUMBER(V14), V14<>0, TRUE), NOT(ISNUMBER(V10)), NOT(ISNUMBER(V11)), NOT(ISNUMBER(V12)), NOT(ISNUMBER(W2)), NOT(ISNUMBER(W3)), NOT(ISNUMBER(W8))), "CHECK INPUTS", IF(OR(INT(W2)<>W2, INT(W3)<>W3, W2<>V2, W3<>V3, INT(W8)<>W8, W8<>0), "CHECK INPUTS", IF(IF(AND(ISTEXT(W4), ISTEXT(W5), ISTEXT(W6), ISTEXT(W7), ISTEXT(W9), ISTEXT(W10), ISTEXT(W11), ISTEXT(W12), ISTEXT(W13)), AND(EXACT(W4, "NOT TESTED"), EXACT(W5, "NOT TESTED"), EXACT(W6, "NOT TESTED"), EXACT(W7, "NOT TESTED"), EXACT(W9, "NOT TESTED"), EXACT(W10, "NOT TESTED"), EXACT(W11, "NOT TESTED"), EXACT(W12, "NOT TESTED"), EXACT(W13, "NOT TESTED")), FALSE), "NOT TESTED", IF(OR(NOT(ISTEXT(W4)), NOT(ISTEXT(W5)), NOT(ISTEXT(W6)), NOT(ISTEXT(W7)), NOT(ISNUMBER(W9)), NOT(ISNUMBER(W10)), NOT(ISNUMBER(W11)), NOT(ISNUMBER(W12)), NOT(ISNUMBER(W13))), "CHECK INPUTS", IF(OR(INT(W9)<>W9, W9<=0, W10<0, ROUND(W10, 2)<>W10, W11<1, W11>5, ROUND(W11, 2)<>W11, W12<1, W12>5, ROUND(W12, 2)<>W12, INT(W13)<>W13, W13<0), "CHECK INPUTS", ROUND(ABS(W9-V9)+ABS(W10-V10)+ABS(W11-V11)+ABS(W12-V12)+ABS(W13-V13), 4))))))

W15, Candidate measurement scale and method audit verdict (enter in W15):

=IF(OR(V8>0, NOT(ISNUMBER(V9))), "STOP: INVALID, BLANK, OR TRAILING SOURCE ROWS", IF(COUNTIF($I$2:$I$16, ">1")>0, "STOP: DUPLICATE RESPONSE IDS IN SOURCE", IF(OR(COUNT($L$2:$L$4)<ROWS($K$2:$K$4), COUNTIF($L$2:$L$4, "<=0")>0, NOT(ISNUMBER(V13))), "STOP: EMPTY OR INCOMPLETE BRANCH COVERAGE", IF(IF(ISNUMBER(V14), V14<>0, TRUE), "STOP: BRANCH ROLLUP RECONCILIATION GAP", IF(IF(AND(ISNUMBER(W2), ISNUMBER(W3)), OR(INT(W2)<>W2, INT(W3)<>W3, W2<>V2, W3<>V3), TRUE), "STOP: EXTRACT WINDOW MISMATCH", IF(IF(ISNUMBER(W8), OR(INT(W8)<>W8, W8<>0), TRUE), "STOP: INVALID CLAIM ROW-CHECK COUNT", IF(EXACT(W14, "NOT TESTED"), "NOT TESTED: ENTER CANDIDATE SUMMARY AUDIT", IF(OR(EXACT(W14, "CHECK INPUTS"), NOT(OR(EXACT(W7, "YES"), EXACT(W7, "NO")))), "STOP: INVALID OR INCOMPLETE CANDIDATE INPUTS", IF(EXACT(V5, "UNKNOWN COLUMN"), "STOP: UNKNOWN AUDITED COLUMN", IF(NOT(OR(EXACT(W5, "NOMINAL"), EXACT(W5, "ORDINAL"), EXACT(W5, "INTERVAL"), EXACT(W5, "RATIO"))), "STOP: UNKNOWN MEASUREMENT SCALE", IF(NOT(EXACT(W5, V5)), "STOP: WRONG MEASUREMENT SCALE (" & W4 & " IS " & V5 & ", NOT " & W5 & ")", IF(NOT(OR(EXACT(W6, "MODE_AND_COUNTS"), EXACT(W6, "MIN_MAX_SPAN"), EXACT(W6, "MEDIAN_AND_SHARES"), EXACT(W6, "MEAN_EQUAL_STEP_CHECKED"), EXACT(W6, "MEAN_UNCONDITIONAL"), EXACT(W6, "SUM_TOTAL"))), "STOP: UNKNOWN SUMMARY METHOD", IF(W14<>0, "STOP: SUPPORTING BENCHMARK MISMATCH (CHECK COUNTS, RATINGS, OR RANK FLIPS)", IF(EXACT(W5, "NOMINAL"), IF(NOT(EXACT(W6, "MODE_AND_COUNTS")), "STOP: ILLEGITIMATE SUMMARY FOR NOMINAL SCALE (NO ORDER OR EQUAL STEPS; USE MODE/COUNTS)", IF(EXACT(W7, "YES"), "STOP: EQUAL-STEP ASSUMPTION DOES NOT APPLY TO MODE/COUNTS", "RECONCILED: DECLARED " & W5 & " METHOD & BENCHMARK CONTROLS MATCH (VERIFY REPORT OUTPUT)")), IF(EXACT(W5, "INTERVAL"), IF(EXACT(W6, "SUM_TOTAL"), "STOP: ILLEGITIMATE SUMMARY FOR INTERVAL SCALE (SUM REQUIRES TRUE ZERO; USE MIN/MAX SPAN)", IF(NOT(OR(EXACT(W6, "MIN_MAX_SPAN"), EXACT(W6, "MEAN_UNCONDITIONAL"))), "STOP: UNSUPPORTED SUMMARY FOR INTERVAL SCALE", IF(EXACT(W7, "YES"), "STOP: EQUAL-STEP ASSUMPTION DOES NOT APPLY TO INTERVAL SCALE", "RECONCILED: DECLARED " & W5 & " METHOD & BENCHMARK CONTROLS MATCH (VERIFY REPORT OUTPUT)"))), IF(EXACT(W5, "ORDINAL"), IF(EXACT(W6, "MEAN_UNCONDITIONAL"), "STOP: UNCONDITIONAL ORDINAL MEAN (STATE EQUAL-STEP ASSUMPTION & PAIR MEDIAN/SHARES)", IF(EXACT(W6, "MEAN_EQUAL_STEP_CHECKED"), IF(EXACT(W7, "YES"), "DECLARED METHOD & CONTROLS CHECKED: VERIFY PROSE ASSUMPTION & DISPLAYED SHARES", "STOP: ORDINAL MEAN REQUIRES EXPLICIT EQUAL-STEP ASSUMPTION (SET W7=YES)"), IF(OR(EXACT(W6, "MEDIAN_AND_SHARES"), EXACT(W6, "MODE_AND_COUNTS")), IF(EXACT(W7, "YES"), "STOP: EQUAL-STEP ASSUMPTION APPLIES ONLY TO ORDINAL MEAN", "RECONCILED: DECLARED " & W5 & " METHOD & BENCHMARK CONTROLS MATCH (VERIFY REPORT OUTPUT)"), "STOP: ILLEGITIMATE SUMMARY FOR ORDINAL SCALE (USE MEDIAN & SHARES OR ASSUMPTION-CHECKED MEAN)"))), IF(NOT(OR(EXACT(W6, "MEAN_UNCONDITIONAL"), EXACT(W6, "SUM_TOTAL"), EXACT(W6, "MEDIAN_AND_SHARES"), EXACT(W6, "MIN_MAX_SPAN"))), "STOP: UNSUPPORTED SUMMARY FOR RATIO SCALE", IF(EXACT(W7, "YES"), "STOP: UNNECESSARY EQUAL-STEP ASSUMPTION FLAG FOR RATIO SCALE", "RECONCILED: DECLARED " & W5 & " METHOD & BENCHMARK CONTROLS MATCH (VERIFY REPORT OUTPUT)"))))))))))))))))))

Continue Reading

Email Notifications

Get notified when new Practical Analytics articles are published.

Field notes on table grain, trustworthy KPIs, and AI-assisted data workflows for small and medium businesses. Sent only when a new guide goes live.

No spam or automated drip funnels. Unsubscribe anytime.