Last week I talked about how you could use if() to automatically figure out the best deal in a game, and promised a more real-life example about grocery shopping this week. To do this, I’ve made a grocery price list that automatically identifies the store or stores with the best prices for the items on your shopping list.
If you want to know more about how to use grocery price lists, Get Rich Slowly has a good explanation, with some useful links. I got the base information for my list from here, but you’ll want to build your own list based on what you actually use and where you actually shop.
In this example, you would enter data in the green and blue columns, and see your results in the yellow ones. When planning shopping all you’d need to update is the numbers in the green “Needed” column. Start by changing all the numbers to zero, then sort by Category and SubCategory. That way if you need milk, once you found one milk you’ve found them all and can decide whether you want 2 half gallons, or one gallon based on the best per-unit price for each, which is displayed in column W. Once you’ve entered all the amounts you want of each item, just sort by the Include column, in descending order. That will put all of your selected items at the top, which isn’t necessary but can make it easier to see.
To enter a new item, just go to the bottom of the list, and enter all of the information in the blue columns. The formulas are already filled in, down to row 1000. If you need to delete an unwanted item just clear the cells in for that row in the blue column, or overwrite them with a new one.
Cell A1 has the total cost of the selected groceries, if all of them are bought in the most price-efficient manner. Cells B1 through H1 have the total number of items at each store, so you can decide if it’s really worth it or not. After all, if a store only has one item at it then it might not be worth going way out of your way to get it.
Next week I’ll talk about some of the formulas and design choices that went into this spreadsheet.