Using spreadsheets in garden planning: Part 2

– Posted in: How-to, Seeds and Seed Starting, Series, Spreadsheets in Gardening, Wishlist
6 comments

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:
Figure 1. Opening screen
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. Figure 2. Format columns as currencyThere 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.” Figure 3. Click on the "Formula" tab

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. Figure 4. Select cell and click 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). Figure 5. Selecting cells to include in the formula 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.Figure 6. Formula done. Just press the Tab keyPress the Tab key and you’re done (Figure 6).

Figure 7. Copy the formulaBut 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). Figure 8. Select cells and Paste 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.

Enter Data

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.

About the Author

Kathy Purdy is a colchicum evangelist, converting unsuspecting gardeners into colchicophiles. She would be delighted to speak to your group about colchicums or other gardening topics. Kathy’s been writing since 4th grade, gardening since high school, and blogging since 2002.

When dealing with frost it is always best to be paranoid. In the spring never think it is too late for one more frost to come. And in the fall never think it too early.

~Rundy in Frost

Comments on this entry are closed.

Carol January 19, 2007, 5:48 pm

Kathy, this is good info. I also use spreadsheets at work, but if I didn’t, I don’t know where I would have learned to do so to any great extent.

Great service you are providing. My 2nd order of seeds has arrived, so it is time to set up this year’s spreadshet and see what else I need!

(I’ve also used a spreadsheet to “map” out my garden. I set up the cells to be the same height and width so it looks like graph paper, then do a “color fill” to show where each raised bed is, then type in what I’m planting in each bed. I probably haven’t described it very well, but it is a useful way to plan something like a vegetable garden)

Kathy Purdy January 18, 2007, 9:22 am

For those who are already familiar with spreadsheets, all one has to do is make the suggestion and a spreadsheet user can take the ball and run with it. My tutorial will be overkill for them. But for those who have never used a spreadsheet, this will help them see the light.

I myself have never used spreadsheets much, so when I made my first one two years ago, I had to rely on the skills of my DH, a bona fide Excel power user. I’m still not skilled enough to provide templates equal to what Jenn referenced in her comment in the previous post, where the user enters in one date, and a multitude of calculations are done for you.

Oldroses January 17, 2007, 8:28 pm

I agree with Zoey. Spreadsheets rock! I ditched my checkbook register and use a spreadsheet. I found a template on the Microsoft site (I use Excel) that had a formula for a running balance. Then there’s my monthly and annual budget spreadsheets and my seed purchase spreadsheets.

Zoey January 17, 2007, 5:17 am

Kathy,
What a great idea to teach your readers how to make a spreadsheet.

I use Excel for everything. I even created a spreadsheet to figure the calories burned and the pounds lost when I was working out.

It’s a skill they can use for many things other than gardening.