The example I shared last week used several different if() statements, for various purposes, and a few other formulas as well, so this week I’m going to walk you through them.
Columns K:V – original data
First, look at columns K through V – the ones with a blue background. That’s the original data, the prices and other information you get from the stores. There are several things to note here:
– Columns P through V (7 columns total) are bordered by a thick black border, and I’ve merged P1:V1 to label all of them Total Price.
– Column N, labelled Qty, is named “Units” because it contains the number of units in the item you’re buying. This is relevant because sometimes you have two differently sized packages, and you want to know which one is really the better deal.
Columns X:AD – Unit prices
Next, look at columns X through AD. If you’ll note, these seven columns are also surrounded by a thick border, echoing the one around the per-store information in columns P through V. That’s because this is another set of per-store information.
In using the spreadsheet you’d never have to touch these columns, but the formulas are essential to making it work. The formulas are all the same (except that I’ve used relative and partially fixed references), so I’ll just go through the one in X4 as an example.
That’s saying that if P4 is blank, leave this cell blank, otherwise return the result of P4/Units. P4 is the Total Price for this item at the first store, and Units is the quantity of units in a package, so P4/Units is the per unit cost.
Column AE – Best Unit Price
I’ve named this column because I’ll be using these results later in the spreadsheet, and the name I chose is BestUnitPrice. The formula in AE4 is:
That’s just saying that the result should be the smallest (or minimum) number in cells X4 through AD4, which is of course each store’s unit price for this item.
That’s it for the basic calculations. As I mentioned earlier, you don’t need to see/mess with these, so they’re off to the right where you won’t see them much.
Now for the things you do need to see, which are in the columns to the left of the main data.
Column J – Needed
This is a number you enter when planning your shopping. If you need two containers of corn syrup, you put a “2” on that line in the Needed column. Anything you don’t need should be zero. I’ve made the background of this column green, so it’s easy to spot.
Column I – Include
This column just exists to make it easy to sort. The formula in it is:
In other words, if you don’t need any of a certain item, leave this cell blank. If you do need some, make it an “x”. With this, you can sort all the things you’re shopping for to the top of this list, and then have them listed in order of category so you can easily see the list of all the “Baking” supplies when you’re in the baking aisle of the store.
Note! In sorting terms, a blank is less than an x. So you need to sort this column in descending order, or else everything you want will be at the bottom of the list, not the top.
Columns B:H – which stores are best / how many at each store.
There’s two things shown in these columns. In rows 4 and below, the formula is:
Remember, columns X through AD are the unit price in each store. So it’s saying that if the unit price at this store is the same as the best unit price, return whatever’s in I, and otherwise leave it blank. Since I is either x or blank depending on whether it’s on the shopping list, the result of this formula is “x” if it’s on the shopping list AND this store has the best price, and otherwise it’s blank.
An equally valid way of doing it would be to use Boolean logic. Here’s some examples.
The top row, B1 through H1, counts the number of times that the best price is at a particular store, by counting the number of times “x” appears in that column. Because it’s at the top, and near the left, it’s very easy to spot without being intrusive. This information isn’t essential, but it is helpful because a store that has only a few best prices might not be worth going to… it might be better to put off buying that stuff until you have more you want to buy at that store, or else paying a little bit more somewhere else to save the cost of gas.
Column A – Total cost
The final column to look at, column A, just calculates the total cost of this shopping trip, if everything on the list is bought. Most of the rows are just figuring out the total cost for that item, and then cell A1 adds all those results together to get the overall total.
The formula I used is another if() statement:
That’s saying that if you don’t need any, leave it blank, and otherwise return the number needed times the number of units, times the best per-unit price. I’m multiplying the best unit cost by the number of units instead of just using the original cost for the item because the individual prices are in separate columns, one for each store. If I were to rely on the original price, the formula would be =IF(Needed=0,””,Needed*min(P4:V4).
I could also have done it without the if(). I could have simply said =Needed*Units*BestUnitPrice. If I’d done that I’d have zeros instead of blanks, which doesn’t look as tidy but is perfectly valid.
Lastly, the formula in A1 is just =sum(A4:A997), which adds up (sums up) all the numbers in A4 through A997. The ending row is somewhat arbitrary, but since I didn’t think I’d ever have 1000 entries in the list, I figured that was enough rows. It would be easier just to do sum(A:A), but since this formula is in column A you’d end up with a circular reference error.
That’s it! It looks like there’s a lot of stuff here, but the logic is fairly straightforward if you think it through. As always, if you have any questions about it feel free to ask in the comments or send me an email message!