Figuring out the best deal with If()

It’s pretty easy to figure out the best deal between equivalent items that are priced differently (e.g. different sizes of the same brand & type of food, or the same item at different stores). You just have to divide the cost by some common unit (e.g. price per pound, ounce, square footage, hour, etc.).

So, where does if() come in? Well, you can eyeball for the best ratio, but you can also use a quick if() statement to highlight it for you.

As a really simple example, I’ve made a Google Docs spreadsheet that calculates the best value for buying land in a video game.

In this spreadsheet I’ve entered the information about cost and income, calculated the ratio by dividing cost by income, and then used an if() statement to list the purchase cost only on the rows where the ratio was lowest.

As a note, if I were to divide income by cost, I’d need to indicate which ratios were largest instead. It’s often easy to get confused as to which you want, but I tend to think about it like this. I want the most income, for the least cost, so increasing the income and/or decreasing the cost would an improvement. Therefore…
…if I’m dividing income by cost, then an improvement due to increasing the income would mean a larger number divided by the same thing, so the result would be larger. Therefore I want max().
…if I’m dividing cost by income, then an improvement due to decreasing the cost would mean a smaller number divided by the same thing, so the result would be smaller. Therefore I want min().

Once I’ve figured out whether I want to highlight the minimum or maximum ratios, it’s time to build the if() formula. The basic formula would be:
=if(this_ratio=min(all_ratios),cost,””)

In other words, if the cost divided by the income on this row is the smallest of all the ratios in this table the cell will display the cost. If it’s not, the cell will be blank.

There’s two main alternatives I can think of to this if() approach: sorting, and eyeballing.

There’s two problems with sorting in this context. The first is that very long lists can take a while to sort. That’s not an issue in this case, but in others it might be. The second is that some lists have an intrinsic value in their original order, and so if you sort it you’d then want to sort it back. That’s actually true in this example – the land purchase options are always listed in the same order. So if the third row of the table is highlighted, I want the 3rd purchase option.

The other approach, eyeballing, has three problems. The first is that you might, quite simply, miss the lowest value. Quick, which is lower – 1234578979.31 or 598762139.793? The second is that in any decimal number format, the value you see might be rounded up or down from what’s actually there. In the list 5,5,5 the numbers look the same, but it might be that what’s actually there is 5.0, 4.5, and 4.9. Third and last is the fact that if there’s more than one equal to the minimum, you might not catch them all.

The Google Docs example has three tables. All are equally valid, and produce the same results – it’s just a matter of how complex you’d want it to be, and how comfortable you feel straying from the actual numbers. If you look at them you may notice that they all highlight 3 rows as “best”. That’s deliberate, since sometimes there are multiple best options.

In the Most Basic/Straightforward one, the numbers are exactly as they appear in the game. This makes it easy to understand, for anyone.

In the Less Typing one, the numbers are simplified – Income is divided by 100, Cost by 1000. Even though this means that the ratio of Income to Cost is skewed by a factor of 10, it’s fine as long as the results are all skewed the same amount. Also, you may notice that I’ve multiplied the value in the “Best” column by 1000, to reflect the actual price. That’s so it’s easy to see whether I’ve saved enough money in the game to buy that piece of land yet.

In the Optimized one, all the user has to update is how many are owned – the table calculates the rest. Also, there’s a Best*10 column, because the game allows you to buy 10 units of land at once, all at the same price. Once you’ve done so the price goes up by 10 increments, but you’ve still saved all the increments you would have paid. For example, the starting cost of a piece of land which gives you 100 per hour is 4000 and the increment is 400. So, if you buy the pieces one at a time the first 10 would be
Total cost=4000+4400+4800+5200+5600+6000+6400+6800+7200+7600 = 58,000

But if you buy the first 10 all at once, it’s
Total cost=4000+4000+4000+4000+4000+4000+4000+4000+4000+4000 = 40,000.

Next week I’ll apply this to a more real-life example: grocery shopping.