Excel Formulas

"Hey, could you help me out here? I'd like to take three of my friends to see a movie—that includes you, of course!

I also don't want to spend more than $50.00. The tickets cost $11.75, so I want to know how much money I'll have left over for drinks and popcorn after buying the tickets.  I'll bet you could use a spreadsheet to crunch the numbers."

Our spreadsheet

Once you've downloaded our spreadsheet, open the file in Excel or another spreadsheet application. We'll just need to enter a few different values to get started. Our friend Darien told us that a ticket costs $11.75, so we'll enter that in cell B1. He'll need 4 tickets (one for himself and three for his friends), so we'll enter 4 into cell B2. Finally, Darien said he didn't want to spend more than $50.00, so we'll enter that in cell B3.

How can we solve this problem?

Darien wants to know how much money he'll have after buying the tickets. To simplify the problem, we're going to break it down into two steps:

1. Figure out how much the tickets will cost.
2. Subtract that amount from the total budget to find our answer.

Writing the formulas

Let's start by calculating the cost of the tickets. To find the answer, we'll multiply the cost of the tickets by the number of tickets needed. Those values are stored in cells B1 and B2. So here's our formula:

=B1*B2

We'll type this formula into cell B4:

OK, the tickets will cost $47.00. Now, we'll subtract that value from the total budget in cell B3. Here's our formula:

=B3-B4

We'll type this formula into cell B5:

So if Darien buys 4 regularly priced tickets, he'll have $3.00 left over for popcorn and drinks.

"Whoa—only $3.00!? I won't be able to buy any extra snacks with that!!

You know, there's also a matinee show for this movie, and the tickets only cost $7.25. How much would I have left over if we went to the matinee instead of the evening show?"

Since we used cell references to write our formulas, we can simply change the ticket cost in cell B1 from $11.75 to $7.25—our formulas will then recalculate automatically.

OK, Darien will have $21.00 left over if he chooses to go to the matinee showing of the movie! Let's send this back to him.

"Cool—thanks for doing this!

I was thinking the matinee would be less expensive, but I didn't realize just how much I'd save overall! I hope you like action movies because one of these tickets has your name on it!"

Bonus section: Alternate solution

In the example above, we used two different formulas to make the problem easier to solve. But if you're already familiar with the order of operations, you might have realized that we could have written it as one formula:

=B3-B1*B2

This formula gives us the same answer as the example above: $21.00. As you may remember from the lesson on Complex Formulas, spreadsheets use the order of operations to determine which operations to perform first in a formula. Because multiplication comes before subtraction, this formula will first multiply the values in cells B1 and B2 and then subtract that value from the budget total in cell B3.

Of course, it's up to you to determine how you want to solve problems in a spreadsheet. However, as you become more confident with using the order of operations, you may find it easier to write only one formula to solve complex problems, as in the example above.