In part 1 we logged into Google Spreadsheets and got things set up. Now let’s get this thing to do some work for us.
Creating the Formula
We’re going to teach this worksheet to multiply the price of a plant by the number of plants we want to give us the total price. The end result will make us gasp and reconsider if we really want that many. In following posts we will also teach it to add up all the total prices to give us the order subtotal. Then we’ll look at other ways to use spreadsheets.
Click here to get back to the Google Docs and Spreadsheets Main Menu. (Bookmark it now if you haven’t already.) You should see something similar to this:
Okay, you won’t see the red circle. “Demo” is what I called the workbook we saved last time. I don’t know what you called yours, but click on the name of your workbook to open it. There are two columns that we want to show up as money, so we have to tell the spreadsheet that. First click on column E, “Price per Package,” to select that column (red arrow, Figure 2). Then click on the down-arrow by the word Format and select the currency format from the list (green arrow, Figure 2). Do the same thing for column F, “Total Price.”
Then click on the Formula tab (Figure 3). Now, creating a formula in a spreadsheet works the opposite of the way you’d expect. You start with the end result and work backwards. So the first thing we’re going to do is select the cell where we want the answer to be. That’s the box right under the words “Total Price,” F2. Then we click the word Product, because that’s the kind of result we want (Figure 4). Next we click the cells of the two things we want to multiply, “Number of packages” (B2) and “Price per package: (E2). It should look like Figure 5. In spreadsheets, as in adding machines (perhaps I date myself), multiplication is symbolized with an asterisk (*). So you need to insert an asterisk between the B2 and the E2. Finally, add the closing parenthesis.Press the Tab key and you’re done (Figure 6).
But wait a sec. You’re not going to order just one kind of plant, are you? We’ve got to make this formula work for all the rest of the rows. Right-click in that cell with the zero dollar amount and choose Copy(Figure 7). Hover your mouse over the cell directly below the one that you put the formula into. Then click and hold down the left mouse button, and drag it down however many rows you think you’ll need. Right-click and choose Paste (Figure 8 ). Now each of those cells will be the product of the B column and the E column of the corresponding row.
I bet you never thought we’d get to this point. Actually, it doesn’t take that long to set up once you know what you’re doing, and now we’re going to watch this spreadsheet work for us. Go through your catalog page by page. Enter information in a cell, then tab to the next. The math is done for you. If you don’t like the total price, go back and lower the quantity. If you decide you don’t want a certain plant, change the quantity to zero, but don’t delete the row. It’s useful to know what you thought about buying but didn’t. I save my spreadsheets and sometimes I order stuff that I decided not to get in a previous year. Other times, it’s just good for a laugh: I wanted that?
In the next post we’ll learn how to find the grand total. To see how far we’ve come today, click here.