The Banana Pickers

Another spreadsheet model is that which solves the 'Bananas' problem. This is as follows:

1. Three men worked hard all day to collect bananas. At the end of the day they put them in a pile and went to sleep.
2. After a while one of the men woke up and decided to eat one banana and hide a third for himself. He then went back to sleep.
3. After a while a second man woke up, ate one banana and took a third of the remaining bananas for himself. He then went back to sleep.
4. After a while the third man woke up, ate one banana and took a third of the remaining bananas for himself. He then went back to sleep.
5. Eventually all three men woke up to find a much reduced pile of bananas but none of them could say anything about it. They divided the remaining bananas into three and each took a third home.
6. As no man took home a part of a banana, how many bananas were in the first pile?

To solve this problem we build a model in a spreadsheet. The input value in the model is cell B2. We want to find a value in this cell which produces whole numbers for all the other values in the model.

In B5 we enter '=B2-1' which is the starting value minus 1, or the number of bananas left after the first man has woken and eaten one. In B6 we enter '=B5*2/3' which tells us what is left after the first man has hidden a third of the bananas.

We continue with the second man, setting B9 to '=B6-1' which is the amount left after the first man has taken a third. We then set B10 to '=B9*2/3' which is the number of bananas left after the second man has taken his third.

You can quickly transfer the formulae from B5:B6 to B9:B10 as follows:

1. Select B5:B6;
2. Hold down the Control key on the keyboard;
3. Point at the border of the selection (an arrow appears with a small cross above it);
4. Drag the selection into cells B9:B10 and release the mouse;
5. Excel performs a copy with relative references just as if you had copied from B5:B6 into B7:B8.

We calculate the amount left after the third man has eaten one and taken a third and we also calculate the amount each man takes home.

We can now enter values into B2 to find which numbers give solutions in whole numbers (integers) only. You should start at 1 and work systematically through 2, 3, 4, etc. until you find a solution. We might call this approach 'trial and improvement' as we work towards a solution. Doing this with pencil and paper, without great mathematical intuition, could take a long time.

There is more than one solution to the problem and the second, third, fourth, etc. solutions form a pattern. The spreadsheet will allow you to implement quickly your guesses for the pattern. (The pattern has something to do with the number of times you divide by 3.)

Finding A Solution: Quicker Ways

Spreadsheets are about problem solving and here is one quick way to solve this problem using standard techniques and a little lateral thinking (don't read this until you have thought about how you might solve it). Put a '1' in B2 and a '2' in C2, select both cells and then drag the copy marker to column IV (you should have 1 to 255); select B5:B16 and drag the copy marker to column IV. Now look for integer values in row 16. 

Another way to speed up the solution is to use an Active-X control such as a 'spinner' or a scroll bar. These can be set so that the value in a given cell can be increased or decreased by clicking on the control, thus saving the bother of typing. To achieve this, proceed as follows:

• Click on the Office button in the top left corner and choose 'Excel Options' from the bar at the bottom:

• Click on the box to show the Developer Tab in the Ribbon

Now you can use the Insert drop-down in the Developer section to add form controls. Start with a 'spin control'.

Right click the spin button and choose Properties (or click the Properties icon in the Control Toolbar). Set the LinkedCell property to B2 and make sure the SmallChange property is set to 1. Set the Maximum value to 3000.

• Click away from the spin control to confirm your settings for the spin button. 
• Set protection on the worksheet to prevent changes to the worksheet formulae.
• You can now use the arrows on the spin button to increase and decrease the value in cell B2 (much easier than typing!).
• You can use the spin button or the scroll bar controls in any similar situation where you want to provide a mouse click instead of keyboard entry.
• Try adding a scroll bar to change the values.