Excel Investigation: More Beads

You might like to read about the Investigation Process .

In case you need to see more of the working for the 3562 beads I used in my example:

At the first person
bead sharer takes 1, leaving: 3561
one third of the beads is: 1187
that third is removed leaving: 2374

At the second person
bead sharer takes 1, leaving: 2373
one third of the beads is: 791
that third is removed leaving: 1582

At the third person
bead sharer takes 1, leaving: 1581
one third of the beads is: 527
that third is removed leaving: 1054

bead sharer takes 1, leaving: 1053
Then divides the remaining
beads into three equal amounts. 351

Here are various Excel files for More Beads which give some answers.

If you look at the formula in any cell you can see what's being calculated.

The spreadsheet for this problem needs to have quite a lot of rows, trying out numbers of beads going from 1, 2, 3, . . . to maybe 500.

This makes very large file sizes, which could take a long time to download.

So I have kept the files as small as possible, still containing the important formula for each process, but leaving you to extend the formula to a more useful number of rows.

Small File More Beads is the smallest file. A11 to M11 need to be replicated (highlight across and as far down as you want to go then Fill Down from the Edit menu)

More Beads 1 is larger, around 200 rows (beads), but at least shows some solutions.

More Beads 2 is the same as More Beads 1 but the commission can be adjusted from 0 upwards.

General Approach

Excel will just crunch away at the calculation because I told it to, but I know it is the non-fraction results that are the real solutions to my problem. Remember the beads are not broken into fractions according to the puzzle. I just need to look for the rows that don't have decimal (fraction) values.

I've also used an Excel trick to help me, called Conditional Formatting. It makes the results which matter stand out. But you could just look for the non-fractions without using conditional formatting.

I tend to have many rows, each with a simple process. It makes things easier to follow. But this does mean that often the end result is no longer in view when I'm looking at the start of the row. I get around this by making the first column just a repeat of the end result. I can then see solutions and start values side by side.