A couple of weeks ago I spent some time in the bible belt attending football games. Next week I’ll be in Bush country (Austin, TX) for yet another football road trip. One of the more exciting aspects of these road trips and looking for things to do in Nashville is settling up on expenses when you get back. Depending on the group, this can take between six days and six months. Here’s a spreadsheet I use to do it:
The formula in E3:I11 is
The first part determines how many times a certain person’s initials appear in the ‘Paid For’ section and takes a proportionate share of the money. The second part subtracts the total expense if this person is the person who paid the bill. For it to work, you have to use the same number of characters for every person, which is why initials works so well. And don’t forget to include that pesky semicolon, particularly after the last guy in the list.
The Hotel1 expense demonstrates some of the flexibility. Note that BD stayed in the room two nights, while TO and FS stayed all three nights. By repeating their initials, each pays his share proportionate to the number of nights he stayed.
I’m sure you could get rid of that $1 rounding error, but it’s not worth the effort. Of course the initials, expenses, and amounts have been changed to protect the guilty, but don’t go thinking that those initials are random.
Now, there’s what Excel was made for, dang it! Never mind all that high-falutin’ accounting usesplitting the beer tab and such is the real utility.
Not sure of your travel plans, but if you’re driving down I-35, I’d be happy to stand you a drink near Dallas.
Why didn’t you simply have a column for each person and put their share of the expenses in it (ie 0, 1, 2 etc)?
Then there’s no need for complex formulae.
My question is why does excel have negative zeroes? This little ‘feature’ drives me nuts in the comma formatting style! Sometimes you get a – for a zero and sometimes you get a (0.00). Any ideas?
Dermot –
A large group of users prefer writing large formulas rather than use helper columns. I understand the challenge in these superformulas, but they do me no good: five minutes after I write one, I’ve forgotten how, so I can’t figure out how to make changes to it. To me, KISS means using extra columns for intermediate calculations.
John –
A negative zero appears where there is a rounding error. Increase the decimal digits in that column and you’ll probably see something nonzero out in the 14th place or so. A dash appears when the cell’s value is exactly zero.
Jon,
I agree wholeheartedly. I really admire the creators of very, very clever SUMPRODUCT formulas, but I’d never give one as a solution to casual Excel users, even if I could :-). My past responsibility for certifying spreadsheet solutions also gave me a preference for intermediate results; so much easier to validate.
Niek
With Jon also.
Just watch what happens when next time you forget to add the ; at the end of a data cell.
First point: Tushar says that BD should only be paying 2/9 of Hotel1 (1/3 of 2 nights) not 2/8 (2 man-nights of 8 man-nights). Since I wasn’t ‘BD’, I won’t be changing that calculation. My story is that we consumed 8 man-nights and he consumed 2 of them.
Second point: This formula is neither large nor complex. I generally agree with your points about helper columns and large, complex formulas, but I don’t think those criticisms apply here.
It isn’t the complexity of the solution that caught my eye as much as the model that mapped the business problem into Excel.
To use an “integrated” approach like Dick used, one must factor in both time and space. When 3 people share a room, each implicitly (or explicitly) gets 1/3rd of the room. With 2 in a room people get 1/2 of the room. The model Dick used incorporated only the time dimension.
[For those who have some experience with Activity Based Costing, this is analogous to using different metrics to allocate different types of fixed/overhead costs. Some might be allocated based on time, some on quantity, some on space, and yet others on some other basis.]
So, to stick with Dick’s approach, break the room into 6 ‘units’ (6 being the LCM of 2 and 3). Now, BD used 2 units for each of 2 nights. TO and FS, on the other hand, each used 2 units for the first 2 nights and then consumed 3 units on the third night. Once one factors in time and space, one would find that irrespective of how many additional nights TO and FS stayed, BD’s cost would be constant at 133.33
With 3 nights (2 including BD), we get BD’s share as 4/18 * 600 = 133.33
With 2 (hypothetical) nights, BD’s share would be 4/12 * 400 = 133.33
With 4 (hypothetical) nights, BD’s share would be 4/24 * 800 = 133.33
That said, there’s nothing as flexible, transparent, auditable, and simple as a method I first encountered in my college days.
For each person, create 2 columns, the first a ‘+’ column, the second a ‘-‘ column.
Now, use one row to record the financial transaction for one event. The definition of an event is, of course, flexible.
For each event, for each person who pays enter the amount they paid in their ‘+’ column. For all those who should have paid enter the appropriate amount in their ‘-‘ column.
The total cost of the event should equal the total of all the ‘+’ entries, which should also equal the total of all the ‘-‘ entries!
To get a “bottom line” number for each person to settle the account at the end, simply total up each column. Take the difference between the ‘+’ and ‘-‘ columns for each person and that’s what s/he owes/is owed.
The ultimate in flexibility, auditability, and transparency. Do this in Excel and one can use formulas in the cells to calculate a number.
Take Hotel1 in Dick’s example. Suppose TO and FS had split the bill. Then, the entries for the row corresponding to Hotel1 would look like:
Total amount $600
TO + $300
FS + $300
BD – $133.33
TO – $233.33
FS – $233.33
Now, suppose the deal was that TO would cover BD’s share while FS would pay just his own share. The entry would now be:
Total amount $600
TO + $366.67
FS + $233.33
BD – $133.33
TO – $233.33
FS – $233.33
The above model would satisfy not only GAAP but even SOX! “Linearize” the above and get a system consistent with a relational database model.
Why separate initials with semicolons rather than spaces? Why require a separator after the last set of initials? Using spaces as separators, allowing arbitrary redundant spaces but not requiring either initial or ending separators, it’s possible to handle IDs with variable numbers of characters.
=(LEN(TRIM($D3))+1-LEN(TRIM(SUBSTITUTE(” “&SUBSTITUTE(TRIM($D3),
” “,” “)&” “,” “&TRIM(E$2)&” “,TRIM(E$2)))))
/(LEN(TRIM($D3))+1-LEN(SUBSTITUTE($D3,” “,””)))*$B3
-IF($C3=E$2,$B3,0)
Then again, if you’re going to do much text parsing in Excel, better by far to use Longre’s MOREFUNC.XLL add-in.
=REGEX.COUNT($D3,””&TRIM(E$2)&””)
/(REGEX.COUNT(TRIM($D3),” “)+1)*$B3-IF($C3=E$2,$B3,0)
[…] Splitting the Check (Excel Tip) by Daily Dose of Excel […]
This looks excellent – but how do you then calculate who, specifically, owes what to who?
Good question of Richard here…
What is still missing is the minimum amount of transactions of who pays who now…
I’m really interested in that one…
Bart
I love this….but has anyone solved the who owes who what part. I’m trying to do this for all the shared gifts my bro/sis bought over the holidays.
Me too – I’ve been waiting for over a year to see if anyone can come up with the answer!