Expense-splitting spreadsheets break at exclusion, not math

Why does leaving one person out of one expense break the whole sheet?

Because exclusion changes the denominator on that row and nowhere else. With everyone sharing everything, each person’s share is the total divided by the headcount, and a single cell does the job. Exclude one person from one row and that row must be divided by one fewer; a person’s share becomes a sum across rows, each row with its own count of sharers. A template that divides by the headcount and then subtracts the excluded person’s share, without redistributing it, under-collects by exactly the amount it removed. A template that divides by the headcount everywhere collects the full total and charges the wrong people.

The 2008 post describes its share formula in prose as “(total expenses / no.of people) – (total expenses excluded for this person / no. of people).” Read literally, that is the headcount version with a subtraction bolted on. Here is what it does on an invented four-person weekend. The numbers are chosen for clean arithmetic, not taken from any receipt.

The sheet itself does better than its prose. Its per-head cell, as stored in the public workbook, is =D4/(10-COUNTIF(who_all,"")-COUNT(E4:H4)): the row amount divided by ten slots, minus the empty name slots, minus the count of excluded people on that row. That is the row’s own sharer count. The share cell then takes the sum of all per-head values and subtracts, for each of four exclude columns, the per-head values of rows where that person’s number appears. On the weekend above, that structure returns $110, $110, $110 and $30, and the dues cell (share minus paid) returns the right settlement: Dee is owed $120, Cal owes $110, Ben owes $20, Ana is owed $10, and the four figures net to zero. The author implemented the per-row rule and described the headcount rule. The comments do not say which of the two any reader was looking at; they do show that, across nine years, nobody in the thread settled what the exclude formula did.

Sources: the prose formula and the ten-slot limit are quoted from the Chandoo.org post (2008); the cell formulas are as stored in the post’s public sheet, exported 2026-10-08. The four-person weekend and every dollar figure in this section are illustrative and computed by us.

What did nine years of readers actually ask about?

Exclusion, the named range it depends on, and the hard-coded slot count. Nobody questioned the SUMIF that totals what each person paid. Nine of the fifteen reader comments that are not tool links or author replies fall in those three buckets. Nobody questioned the subtraction in the dues column. The questions cluster on the one formula a reader cannot check by eye, and the proposed fixes are not all improvements. The dates below are the comment timestamps on the page.

Two of those comments deserve a closer look, because they show that the readers could not tell a right formula from a wrong one. The COUNTIF-versus-COUNTA proposal, endorsed by a second reader, would break the sheet. With ten slots and n names filled in, 10-COUNTIF(who_all,"") equals n, the headcount; 10-COUNTA(who_all) equals 10 minus n, the number of empty slots. The two agree only when exactly five names are filled. On the four-person weekend above the proposed fix would divide the kayak row by five instead of three and return $18 instead of $30. The 2015 comment is the opposite case: a user who had used the sheet “for many trips” found that it “does not like” a row where the payer is excluded. On the stored formulas we could not reproduce a failure for that case; the dues cell handles Dee’s concert tickets correctly. The comment does not say which version of the sheet it refers to, and the first commenter, in a follow-up six minutes after the opening comment, had already reported that “your version in the download is different from what you have in google docs.”

Source: comments on “How to: sharing trip expenses using excel,” Chandoo.org, as read on 2026-10-08. The COUNTIF-versus-COUNTA arithmetic is ours.

What does spreadsheet-error research call this kind of bug?

A logic error wrapped in a latent one. A widely used taxonomy comes from Panko and Halverson in 1996. In Panko’s own restatement, mechanical errors are “typing errors, pointing errors, and other simple slips”; logic errors are “incorrect formulas due to choosing the wrong algorithm or creating the wrong formula to implement the algorithm”; omissions are “things left out of the model that should be there.” A headcount denominator on an exclusion row is a logic error: the formula computes something, just not the right thing. The reader who proposed COUNTA was proposing a logic error to fix a formula that had none.

The hard-coded 10 is the other category. Putting a number into a formula instead of a cell reference is, in Panko’s account, “the most common qualitative error.” It “does not cause errors then, but it makes errors more likely later, say when assumptions have to be changed,” which is why he files it under what James Reason called latent errors. The reader who asked how to go from ten people to three in 2014 was asking how to change that assumption. In Powell, Baker and Lawson’s audit of 50 operational spreadsheets, hard-coding was the most common error type at 37.7% of the 483 instances they found, ahead of reference errors at 32.9% and logic errors at 21.9%. Most of those hard-coding instances were classed as poor practice rather than wrong results; among the cells that actually produced a wrong result, logic errors led at 45.6%, reference errors followed at 36.4%, and hard-coding accounted for 8.1%. Hard-coding was the common latent error in that audit; the wrong results were mostly logic and reference errors. Those are audits of 50 operational workbooks holding 270,722 formulas between them, not trip sheets, so we borrow the categories and not the rates.

One more result is worth an analogy, and the analogy is ours. In Panko’s inspection study, “omission errors were indeed detected much less frequently than other types of errors.” That study is about things left out of a model, not wrong denominators, and it says nothing about how detectable an exclusion bug is. What it suggests to us is that errors with no visible symptom are the hard ones to catch, and an exclusion that silently reverts to a headcount share has none: a number sits in every cell, and the only sign is that one person pays for a kayak they never sat in.

Sources: Panko, “Revisiting the Panko-Halverson Taxonomy of Spreadsheet Errors,” Proc. EuSpRIG 2008 (a revised version appeared in Decision Support Systems 49(2), 2010, with Aurigemma); Powell, Baker & Lawson, “Errors in Operational Spreadsheets,” Journal of Organizational and End User Computing 21(3), 2009. The quoted definitions are Panko’s wording; Powell et al. restate the same three Panko-Halverson types in their literature review, then adopt a six-category taxonomy of their own in which “omission” means a formula pointing at a blank cell. The instance shares are from Powell et al.’s Table 3 and the wrong-result cell shares from their Table 4.

What happens when an exclusion error reaches a famous spreadsheet?

In the best-known case, it went unnoticed until someone replicated the result. An exclusion error produces a plausible number, and nothing in the sheet flags it. The example is the working spreadsheet behind Reinhart and Rogoff’s 2010 finding that average growth drops sharply once public debt passes 90% of GDP. In their April 2013 paper, Herndon, Ash and Pollin report that “a coding error in the RR working spreadsheet entirely excludes five countries, Australia, Austria, Belgium, Canada, and Denmark, from the analysis.” The mechanism, in their footnote: “RR averaged cells in lines 30 to 44 instead of lines 30 to 49.” Five rows, five countries, one range that stopped short.

The parallel to a trip sheet is not the economics, and the mechanisms differ: the economists dropped rows by accident, while a trip sheet excludes people on purpose and then has to redistribute their share. What the two share is the symptom. The error was an exclusion, the excluded rows were “selected alphabetically,” the result looked like a result, and the public data behind it came, in the critics’ words, “with complete source documentation.” The error lived in the working sheet, which the critics could only check once the authors sent it to them. Nothing about a range or a denominator announces itself. If a working spreadsheet that shaped a policy debate could drop five countries by stopping a range five rows early, a borrowed trip template can drop one friend from one row and nothing on the page will flag that either.

Source: Herndon, Ash & Pollin, “Does High Public Debt Consistently Stifle Economic Growth? A Critique of Reinhart and Rogoff,” PERI Working Paper 322, April 2013 (later published in the Cambridge Journal of Economics 38(2), 2014). The −0.3 point figure is the authors’ attribution to the spreadsheet error combined with other errors; the −0.1 to 2.2 percent gap is their full correction.

How do the popular expense-splitting templates handle exclusion?

Differently, and the design choice decides what can go wrong. We read the documentation pages of five published templates, the Chandoo sheet among them, and recorded how each one lets a user say “Dee did not share this.” Only one of the five pages describes the denominator as a count the user can see.

The 1-or-0 design is the one to copy. A row of ones and zeros makes the denominator a visible count, lets “everyone shared it” be the default row, and turns exclusion into flipping a 1 to a 0. The exclude-number design does the same math in a form nobody can audit, with a ceiling of four exclusions and a slot count baked into the formula. The share-entry design, Indzara’s, is the most flexible and the most work: every unequal row needs the shares typed in by hand, and the template ships validation rules that flag a row whose typed shares do not sum to the amount. Its author also notes that equal distribution can leave “a rounding difference of a few cents,” a caveat for any design that divides and then rounds without placing the leftover cents on purpose.

Sources: Chandoo.org (2008); Spreadsheet Daddy, “12 Spreadsheet Templates for Splitting Expenses,” entry 2 (Al Chen’s Google Sheet); Indzara, “Group Shared Expense Calculator”; TrumpExcel, “Shared Expense Calculator,” last updated 2026-04-20; brsanthu/expense-share-calculator README. All as read on 2026-10-08; “not described” means the page we read does not say, not that the file cannot do it.

How should a split sheet model exclusion?

As a grid of people against expenses, with the payer kept out of the exclusion logic entirely. This is our prescription, not a quotation from any of the sources above, and it is what the 1-or-0 template does with a few checks added. Six rules cover it.

On the illustrative weekend the grid has three rows, four columns, and exactly two zeros: Dee on the kayak row and Dee on the concert row. Shares come out $110, $110, $110, $30 and dues come out −$10, $20, $110, −$120. Both checks hold. Turn the two zeros back to ones and the sheet gives $90 each, with dues of −$30, $0, $90 and −$60. Both checks still hold, which is rule 6’s point: no sum catches a wrong flag, so the grid has to be readable by everyone who is on it.

The six rules and the formulas are our own prescription. The 1-or-0 participation design they build on is the one Spreadsheet Daddy describes for Al Chen’s template. All figures are from the illustrative weekend above.

Why is a restaurant receipt the same problem in miniature?

Because a receipt is a participation grid too, and on an ordinary dinner several lines carry their own sharer count. Imagine the fries shared by three people, the dessert by four, the bottle by two, and one person who drank water and sat out every drink line. That is the trip-sheet exclusion problem compressed into one evening, with the extra wrinkle that tax and tip then have to follow each person’s items. splitty’s own receipt data give a sense of what those lines are: across splitty’s US-leaning restaurant receipts, nine of the ten most-ordered line items are drinks, and french fries is the only food in that top ten. That ranking counts how often an item name appears, not how often anyone sat a line out. Our own reading, which the ranking does not measure, is that a drink is usually one person’s line rather than the table’s, so it is the kind of row where the in-or-out question gets asked. The mental math article covers why humans cannot hold that many denominators in working memory, and the calculator comparison covers why typing the lines into a calculator moves the error from the arithmetic to the transcription. Both are the same lesson as the Chandoo thread: the division is easy and the bookkeeping of who shared what is where the split goes wrong.

The fries, dessert, bottle and water dinner is illustrative, not taken from any receipt. The most-ordered-items fact is splitty first-party data: the rank order of line items across splitty’s own US-leaning restaurant receipts (2026 snapshot), scoped to that sample and not extrapolated.

A trip ledger and a dinner receipt do differ in one way that matters for tooling. A trip accumulates rows over days and ends in a settlement; a receipt is one document settled once. The honest positioning is that a multi-day trip wants a ledger tool built for that, and a dinner wants something that reads the receipt and makes the per-item exclusion a tap. Splitting a multi-day trip? Try Splid. Splitting tonight’s dinner? That is what splitty is for.