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 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.