Building Formulas: Spreadsheets, algebra and guest lists

The classic answer to the question, “When am I going to use algebra?” is spreadsheets. Now I will admit straight up — I am a spreadsheet junkie. I’ll build one for just about anything, from menu planning to blog schedules to tracking which clients have sent me 1099s. And I know for a fact that this attraction to spreadsheets is not normal. I promise, I will not try to convince you that spreadsheets are the be-all-end-all (though I think they are) or that using a spreadsheet will make your life easier (though it could).

But there’s no denying one important thing: algebra is extremely useful in spreadsheets. And that’s because the power of a spreadsheet is in its ability to crunch numbers for you.

In fact, algebra teachers are using spreadsheets to help students better understand algebra and its real-world uses. Want to see how data is related? Use a spreadsheet to create a line of best fit. Want to find an average quickly? Spreadsheet. Want to know how many children and adults you’ve invited to your wedding? Open up Excel or Numbers or OpenOffice Spreadsheets.

(Yes, we’re back to the wedding. It’s consuming my life right now, so you get to play, too.)

When it came time for me to create the guest list for my wedding — undeniably the most painful part of this entire experience — I naturally reached for good-old Excel. Once I had everyone entered into a spreadsheet, I was able to create a variety of formulas that have helped me manage certain tasks. Here’s an example.

Our reception venue, which also provides the catering, offers a much lower rate for children. They’re getting chicken tenders, rather than the fancy-schmancy meal, so that’s only fair. But there are a lot of kids on our list, and I needed to get a rough estimate of what we would pay. This way, I could make really good decisions about who we could and could not invite. (Told you, this part was really painful.)

Each family, couple or person was listed in one row of the spreadsheet. In two of the columns for each row, I included the number of adults and kids who were invited.

So you can see that my sister, Melissa, has 4 children under 16 years old, while my mother and two brothers don’t have any. But each of their families has 2 adults. The column all the way to the right is the total people in their families who are invited. In fact, I used a very simple formula to find the last column: =SUM(B2:C2). This means, “Take the sum of the values in columns B2 through C2.” The formula allows me to make changes to the values in columns B2 through C2 and automatically update the last column.

But that’s not really where the algebra comes in. At the bottom of my spreadsheet, I use the SUM formula to total the number of kids and the number of adults. Then I use those values to find the cost of the reception food, using a formula I built. Here’s how that worked.

Let’s say I’ve invited 15 kids and 100 adults. I’ve let my spreadsheet automatically find those totals in cells B101 and C101. And let’s say that the cost per adult is $50 and the cost per child is $25. Algebra will help me create a formula based on the cells where this data is found.

=((B101*50)+(C101*25))

Looks ugly, right? Well, that’s because the spreadsheet needs some extra formatting to recognize the formula. But there’s a simpler way to show this:

y = 50a + 25k

In other words the total cost for the food (y) is equal to 50 times the number of adults (a) plus 25 times the number of kids (k). Algebra at work in the real world of wedding planning.

My job today is not to explain the algebra to you step by step. But I did want to demonstrate one really useful — and somewhat common — way that a regular person uses algebra in their regular life. (Okay, so maybe I’m not regular, but hopefully you get my drift.)

Do you use spreadsheets? What formulas have been useful to you in your spreadsheets? Did you think of that as algebra? Why or why not? Share your thoughts in the comments section.