Aug 302013

As I mentioned earlier, I noticed a few things during my wedding planning process that I thought were worth sharing now that all is said and done on that front. While I mentioned having a Google Drive spreadsheet in my last post, I wanted to discuss it some more because we managed to get some neat uses out of it that are worth documenting and reusing.

Google Drive is very useful for organizing guest list, it’s 1 document, that you, your fiance, and everyone else that needs to see a guest list can update simultaneously. It’s also easily shareable to anyone else that needs to see your guest list. While trying to organize our guest list, my fiance and I wound up coming up with some useful formulas in our guest list that were a huge help in our planning process. The way we organized our guest list, we sorted by guest last name, grouped adults into 1 column, and kids into another. This let us break out our guest counts by adults and children later (which helps get more accurate pricing estimates). We also had a column with what we referred to the guests as, leaving us 2 columns we could use to look up guests, 1 easily sortable column with a “Mr. and Mrs. Some Guy’s Name” that matched what we would put on the outside envelope of a wedding invitation, and another that lined up with the name we used when referring to guests. The “what we call them” column helped remind us about who guests were when we mainly knew a wife and not the husband.

The first formula is one we used to count the number of adults we were inviting to the wedding. Our adults column took 1 of the 2 followings form:

<Last Name>, Mr. and Mrs. <First Name>

<Last Name>, Mr./Mrs. <First Name>

There were obviously slight variations for single people, people with just a girlfriend/boyfriend, homosexual couples, etc., but you get the basic idea. Then counts the number of people listed in that column:

=IF(ISNUMBER(FIND(“and”,<Adults Column>)),2,1) (Source)

Short and simple, if the word “and” is in the <Adults Column>, there are 2 adults invited. Otherwise, there’s just 1 adult coming. So, for Parents, Mr. and Mrs. My the formula lists 2 adults invited. For Uncle, Mr. My, it listed 1. Short, simple, straightforward, works no matter if it’s a couple living together or a married couple who never got 1 last name, you name it. Add this to a guest list spreadsheet and you now have a very quick and simple way of know the number of adults getting invitations.

Next up is a formula that I used  to count the number of kids invited to our wedding. Unlike with the grown-up couples, this doesn’t work off a simple “and means 2, otherwise just 1” trick. There’s no telling how  many kids some families may have included with them, so we need a more useful formula. We just did a simple comma delimited list of kid names, something along the lines of

<Kid 1>,<Kid 2>, etc…

The formula for counting these names up looks like:

=IF(LEN(<Column with kids’ names>) = 0, 0, SUM(ARRAYFORMULA(LEN(<Column with kids’ names>)))-LEN(SUBSTITUTE(<Column with kids’ names>, “,”, “”)) + 1) (Source)

This formula just counts the number of commas in the column with all the kids names in it, and adds one (for the lonely little kid at the end with no trailing comma). The -LEN(SUBSTITUTE()) bit was to fix a math issue I was having, I think with columns where no kids were included (not everyone my wife and I know reproduced).

OK, so we’ve counted the number of people we’re inviting, and can break those counts up by number of adults and number of children. Next up, what every guest list needs are addresses. We broke addresses on our sheet to 1 column per address line, so each row had a set of columns that went like:

<Street address 1>  <Street address 2>  <City>  <State> <Zip code (Postal code, if you want to be proper about it)>

This formula gets us back to simple:

=IF(ISBLANK(<Street Address 1>), IF(ISBLANK(<City>), IF(ISBLANK(<State>), IF(ISBLANK(<Zip Code>), 1, 0),0),0),0)

In short, it checks to make sure there’s text in the the bare minimum fields it takes to form a coherent address in the United States – the <Street address 2> field was for things like apartment numbers, which not everyone has. Since checking for the existence of optional data does nobody any good, just ignore it. If any of these fields is empty, return a 1. Otherwise, return a 0. Why the binary output? Because I’m putting the output of this formula in its own column, and can then add that column up to get a count for the addresses I need. Just for fun, I slapped some conditional formatting on the count of addresses I need that said “Anything other than 0 print in bold red text”.

The last formula we used for our wedding guest list is probably the 1 that I found the most useful. If you paid close attention to the first 2 formulas in this post, you’d have noticed that they counted the total number of people being invited, not the number of people we expected to show up. Obviously, not everyone can make a wedding, but different people can have wildly different chances they’ll show up (the odds my wife’s brother who was living near us would come to our wedding, pretty good, the odds my mom’s extended family from several states away would come to the wedding, slim to freaking none). If only our guest list was in some type of format where we could calculate the chances these people would show up on an individual basis, like…a spreadsheet!

So here’s what we did, first, we created a column that held the percent chance we thought these people would show up. No decimals, just whole number representations of the chance they’ll come. There’s no formula for filling in those numbers, just purely arbitrary guesses on your part. That’s fine, so long as when you’re being arbitrary about all of this, you’re being arbitrarily high. The only hard-and-fast rule for this field is that the number needs to be greater than 0. A lot of your reception money is based on head count, so if you’re going to err, err high. Besides, this is a wedding, people make more of an effort to attend these things than one would otherwise assume. The next column after that is where the math comes in. It has a formula that looks like:

(<Sum of children and adults invited>) * (<Percent chance they’ll show up>/100)

Doing this for all of your guests and adding the values up gives yo a weighted headcount. In other words, if you were to mail out invitations, this is your best guess as to the number of people that would actually show up. Since you probably know how many people you can afford to have actually attend your wedding, and that not everyone you invite can show up, this gives you a good way of trying to invite everyone politics says you should invite without letting the number of people you actually plan on showing up get out of hand.

So, let’s review the wedding guest list spreadsheet on Google Drive. In the first column, we have the adults, ordered by last name, with Mr. and Mrs. for the couples. Next up is a list of kids included in families that you intend to invite. Nothing special here, just a plain old comma-delimited list. Next up we have a column with whatever names we refer to these people by (wedding invitations have 2 envelopes both of which need to be addressed, stupid I know, but it has to be done – these names go on envelope #2, the first column is used for envelope #1). Now starts the math, because the next column is is our formula counting the number of adults invited to this shindig, followed by our formula counting all the children. After that, we have a column with a simple little formula adding up the number of adults and children. After that, we have our percent chance the guests will attend, followed by our weighted average formula. The next 5 columns are all the address columns, street address, apartment number, city, state, and zip code. Lastly, we have our address needed formula, creating our boolean 1/0 value for if you’re missing an address or not.

Now that we have all these formulas with all these fancy counts, let’s do some fancying totaling so you can get a high-level view of what’s going on with your guest list. I had 1 field for the total number of invitations going out, and then another couple of fields breaking that down into kids and adults. I also had a field adding up the weighted average we calculated. This gave us a good working estimate of what our real head count was looking like with our guest list, and helped us know how many people we could safely send invitations to without tearing up our budget. Finally, I had 1 little cell holding the number of addresses we still need to get (the sum of all those 1s and 0s for our address formula).

I hope this breakdown of how we used a Google Drive spreadsheet to organize and plan our guest list and keep track of head counts and addresses. Depending on how you like to organize things, you may want to tweak the formulas or break things down differently. The benefit of all of this is, everyone can update the guest list, so if 1 of our parents gets an address of some semi-distant relative they can just put it in themselves. It’s also all 1 document and 1 version, so there’s no need to send a bunch of copies of the guest list around or keep track of. Overall, I found this guest list to be incredibly useful and maybe you will too.

 Posted by at 1:46 AM