Using spreadsheets in garden planning: Part 3

In our last part we had finished entering data and were getting ready to sum up. Actually it’s best to create this autosumming Subtotal before you enter much data. That way, you have a good idea how much your wishlist is costing you as you go along.

Creating a Subtotal

The first thing you need to do is make an educated guess as to how many items you’re going to be wanting from this particular merchant. The Subtotal formula will be constructed in a similar fashion to the one calculating total price for an item. First, click on the Formula tab (Figure 1). Then click on the cell in which you want the Subtotal to appear. Most people would pick a cell in the Total Price column several rows down from the first item, but you could also put it up at the top in an unused column. Shoot, you could even put it both places by copying the formula. We’ll be traditional and put it in the classic place, about 20 rows down (Figure 2).
Then click on the word Sum. Now we have to select all the cells we want to be added. That would be F2 through F20, giving us room for 19 items.Notice as you drag your mouse down the column, the cell label increases, showing you the range of cells that are going to be added together (Figure 3). Unfortunately, I couldn’t capture that in a screen shot. When you’ve selected all the cells, stop. Then add the closing parenthesis (Figure 4). Then press the Tab key on your keyboard, and if all went well, your potential purchases should be added up in the cell you designated. If you left room, you can continue to add new items and the total will automatically be readjusted. Decide not to get something? Zero out the quantity and tab to the next cell. The total is recalculated. Want more or less? Change the quantity, and the rest is changed for you. That’s the real beauty of the spreadsheet.

Final Touches

Let’s label the subtotal so we can pick it out better. And let’s include the box charge so we have a better idea of the total we’re racking up (Figure 5). Calculating the shipping is a bit trickier. We first will have to check if the subtotal is more than $70. If it is, the shipping is 10%. If the subtotal is less than $70, the shipping is a flat $7. And we need to remember that our subtotal cell is F21. The first thing we need to do is get back on the Formula tab. Then select the cell where we want the grand total, as shown in Figure 5. Next, click on the more link at the far right of the list of functions (Figure 6). When you do that, a list will come up. Choose the Logical category (Figure 7). Then select the IF function. Figure 8 shows the pattern for our formula. We need to erase the words that are there and substitute our own values. What we are testing for is whether or not our subtotal is more than $70. So you need to erase the word test and type F21>70. (Keep the comma after the 70.) You don’t have to add the dollar sign because that is added by the formatting. If the subtotal is over $70, what do we do to find the shipping? We multiply by 10%. So, where it says then_value, erase that and type in F21*.10 (leaving the comma once again). And if the subtotal is not over $70, the shipping is a flat $7, so where it says otherwise_value just type 7.When you’re all done, it should look like Figure 9. Hit the Tab key and your formula is in place. Woo-hoo!

Now, can you create the Grand Total yourself? (Hint: you need to use the Sum function again.) Mine looks like this now that it’s all done (Figure 10):
Yes, my grand total is bolded green. Why? Because I wanted to be able to see it easily, and I think green is a good color for a gardener’s spreadsheet. The more observant among you might notice that the numbers are different, too. That’s because I decided I wanted to get the Jack-in-the-pulpits from Seneca Hill after all, so I zeroed them out, and the change was immediately registered in my subtotals. View it here.

More Examples

For those of you already familiar with spreadsheets, I’m sure I went into way too much detail. For those of you who have never used a spreadsheet before, I hope I told you enough. If I didn’t, well, you know how to comment, right? I’d like to know if this helps you, as well. I’m going to publish one more part to my tutorial after this, mostly to discuss other ways you can use spreadsheets in garden planning.