Puzzles to Help Make Decisions with Spreadsheets

Puzzles to Help Make Decisions with SpreadsheetsIn my last post, we considered using spreadsheets as an aid in making decisions. In preparation for showing some explicit examples using Excel, I decided to devote at least one post to some puzzles which have bearing on the decision making process and might help to orient one’s mind toward programming a spreadsheet while also being fun. The following three puzzles were chosen as examples of simple logic manipulation, probability computation, and game strategy.

The puzzles were adapted from Nick’s Mathematical Puzzles. He has many more, some of which would be appropriate for this topic, but all of them could be fun.

(1) Spreadsheets for making decisions will often use the simple functions “abs” and “max,” where abs is the absolute value defined as the magnitude of a number regardless of sign. For example, abs(4) = abs(-4) = 4. Similarly the max function returns the larger of two values (for simplicity we will not consider more than two, but enthusiastic puzzle workers are welcome to take that as a challenge). For example, max(4, -6) = 4.

We use these simple functions without a thought, but the challenge here is to:

(1) Define abs in terms of max and
(2) Define max in terms of abs.

That is, using only arithmetic functions (+-*/), integers if necessary, and parentheses, define abs as a function of max. Define max as a function of abs.

This sort of low level manipulation of primitive functions is often useful in programming logic arrays. It can be very useful in setting up logical conditions in a spreadsheet, although as a practical matter, one will likely never have to perform such low-level logic operations because modern spreadsheets have all them and many more functions readily available.

(2) We often hear the term “sum-zero game” in decision problems. Here is a number game which quite literally aims for a sum zero in a game. Start with two players and an array of numbers {-4, -3, -2, -1, 0, 1, 2, 3, 4}. Players take turns selecting a number and removing it from the list. The first player to get three numbers that sum to zero wins. Can either player have a strategy with a guaranteed win?

This is the almost the form given at the site referenced above. You should have no difficulty in coming to an answer. However, we can make it more challenging by starting with a larger set of numbers. Pick any upper limit as long as you have as many negative as positive numbers in order (plus 0). Now we can think of two variations of the basic game. The first is to play the same way and strive for three numbers that add to zero, but now play it that the number adding to zero must be three chosen in order (selection n, n+1, n+2), or as another variation, get any three numbers to add to zero regardless of the order in which they were chosen.

A final variation seems to be even simpler. The first person to get any combination of any amount of numbers that adds to zero wins.

I will feature some correct analyses in a future post. Anyone who extends the number array to infinity and derives a valid strategy will get special recognition (I am not sure I did it right!).

(3) Some readers of this column surely have played craps at some time or another. Two dice are thrown and adding to 7 is special in that game. So is the combination of two 6s. The frequency with which a 7 is thrown is critical. So let us consider how likely it is to throw a 7 versus throwing boxcars. Which is more likely, throwing boxcars or throwing three sequential 7s? Compute the relative probability. Would you bet that boxcars would come up before three sequential 7s?

Try to work these without hints first, but if you need some help, here are some hints — maybe not very helpful hints. I do not want to make it too easy.

Hints (or obscurations, maybe):

(1) Expressing abs as a function of max is the easier of the two. I flashed on the answer to expressing max as a function of abs quickly by analogy with a famous way of improving an estimate of a square root — that might sound obscure, but will be obvious when I post both the answer and the algorithm for improving the square root.

(2) This first part is so obvious that it needs no hint. The extension to arbitrarily large limits is straightforward for the three-element variations. No comment on the last variation.

(3) Remember that the probability of two events happening is the product of individual probabilities. The probability that either or both of two events will happen is different. Start by computing the probability that a 7 will be thrown.

After working these puzzles, as a bonus, consider how a spreadsheet like Excel works and see if you can implement (1) and (3) in Excel. Implementing (2) would be more of a challenge.

Article Written by

  • http://www.youtube.com/whiterazor White Razor

    Here’s me using spreadsheets to solve Assassin’s Creed puzzles! http://www.youtube.com/watch?v=CRKBa9f9tjc

    Probably not 100% about what the article is about, but similar to what Chris’ video title is (“How to Use Spreadsheets to Play Games”)