Make 200 task: can you help me work out a formula to solve this quiz?

**Alex Brown** · 11-01-2009, 02:13 PM

Hi there,

I was wondering if anyone could help me make an Excel sheet to solve the following quiz:

**darkyam** · 11-01-2009, 03:53 PM

Hi, Alex, welcome to the forum. We don't provide solutions to homework here. To give you a few pointers, though, you can set up a sheet to go through all possible iterations (I would recommend allowing duplicate numbers since it's easier to do that and filter out those that have duplicates in them than to not allow them in the first place) and then set formulas to add up the numbers using &, for example: =(A1&B1)+(A1&C1)... Apply a filter to the results when you're done and set it to look for the results that add up to 200.

**martindwilson** · 11-01-2009, 08:10 PM

i think the answer is well i wont tell you but its more than 15 and less than 30!,i did it by
1. generating all the 4 digit permutations of 4 from 1-9 there are 3024 permutations once all those with 0 or 2 or more digits the same are removed)
1234
1235
1236
1237
.
.
.
9874
9875
9876

splitting with text to columns

then splitting each into groups of 4 so 1234 [by combining(concatenating) the cells ] became 12 34 13 24
i then copied past back special to get values only
then totalled them (at this point they are text numbers sum wont work but b1+c1+d1+e1 will)
or by changing those 4 groups to real numbers (there are several ways to do that)
then total them using sum
tip
to generate all permutations without code
enter 1234 in a1
1235 in a2
drag down to auto fill until you reach
9876 which is the highest number with digits 1 thro 9
changed that column to text

use auto filter to remove those with 0 in
split with text to columns
then identify all those with duplicate values e.g 1244 (countif is handy here)
used auto filter to remove those
if you get stuck come back and ask how to do any bit!

**daddylonglegs** · 11-01-2009, 09:29 PM

My approach would be to narrow down the possibilities rather than examining them all, you wouldn't really need Excel.....

Let's call the squares TL (Top left), TR, BL and BR

The digits in the TR and BL must either both be even or both be odd.....ignoring the order for a moment that means there are only 16 possible pairs, e.g. 1 and 3, 1 and 5, 1 and 7, 1 and 9, 3 and 5 etc.....

You also know that if you multiply the sum of TR and BL by 11 and subtract the result from 200 you get double the two digit number that forms TL and BR, e.g. take my first pair, 1 and 3, sum those (4), multiply by 11 (44) subtract from 200 (156) and halve that result and you get 78....so one possibility is 7, 1, 3, 8 (and another is the same combination with TR and BL reversed, i.e. 7, 3, 1, 8)

So all you need to do is list the 16 possible (reversible) pairs for TR and BL then calculate the result for the other 2 squares and eliminate any where digits are duplicated.....