Hi Folks,
I was wondering if the following puzzle can be put in a formula, with
perhaps IF statements or however one can decide to solve. I was
thinking of a spread sheet where we can focus on 5 cells eg. A1 to A5
and assume each as a house and write formula or code:

Here it goes:

there are five mebn of five different nationalities, living in five
different houses, of five different colors, each with different pet,
cigarette and beverage. The pets are a dog, a camel, a giragge, an
elephant and a horse. The mena re a Scotsman, a Norwegian, a German an
Englishman and an Irishman. The men smoke Camels, Pall Malls, Kools,
Salems and Chesterfields. The colors of the houses fare red, brown,
yellow, blue and green. The beverages are tea, juice, offfee and
water.

1. The Yellow house is immediately to the right of the green house and
has only
one neighbor.
2. Milk is drunk in the middle house.
3. The man in the red house lives immediately to the left of theman
who smokes
Chesterfields.
4. The Norweigian doesn't drink milk.
5. Tea is drunk in the house next to where the Scotsmnan lives.
6. The Scotsman smokes Camels.
7. The Irishman lives immediately to the left of theman who smokes
Pall Malls.
8. The dog lives in the house next to the house where the Irishman
lives.
9. Ther German lives next to the blue house.
10 The Germandrinks coffee.
11. The Giraffe and the elephant lives in houses separated by the blue
house.
12. The man in the green house smokes Salems.
13. The Englishman lives to the right of the house where juice is
drunk.
14. The horse lives in the niddle house.
15. The camel lives next tot he house where Kools are smoked.
16. The Scotsman lives in the brown house.

Now, who drinks tea? Who owns the horse? Name all five things for all
five homes.


Good Luck
Jaz


--
jazbath
------------------------------------------------------------------------
jazbath's Profile: http://www.excelforum.com/member.php...o&userid=29467
View this thread: http://www.excelforum.com/showthread...hreadid=491735

I was wondering if the following puzzle can be put in a formula, with
perhaps IF statements or however one can decide to solve. ...

Hi. I find these are indeed fun puzzles to solve using Excel.
However, I doubt one would be able to do this on a worksheet.
To do a brute force method, there are ...

=POWER(FACT(5),5)
24,883,200,000
combinations to search.

The method I use is to use programming. I find the best method is to use a
Dictionary object, and add one groups (ie Colors, beverages, etc) and then
try to use clues to delete as many as you can. Then, and the next group.
Doing it this way, the max size of the array is usually around 4,000.

For example, with clue #1, you add the color permutations (120 of them), and
then use the clue to reduce the size back down to 6. (ie the last two houses
are green & yellow) Adding the next permutation to these 6 keeps the size
of the array small and manageable. Eventually, the array size is one..your
solution.
It's a fun program. Good luck. HTH. :)

--
Dana DeLouis
Win XP & Office 2003


"jazbath" wrote in
message ...

Hi Folks,
I was wondering if the following puzzle can be put in a formula, with
perhaps IF statements or however one can decide to solve. I was
thinking of a spread sheet where we can focus on 5 cells eg. A1 to A5
and assume each as a house and write formula or code:

Here it goes:

there are five mebn of five different nationalities, living in five
different houses, of five different colors, each with different pet,
cigarette and beverage. The pets are a dog, a camel, a giragge, an
elephant and a horse. The mena re a Scotsman, a Norwegian, a German an
Englishman and an Irishman. The men smoke Camels, Pall Malls, Kools,
Salems and Chesterfields. The colors of the houses fare red, brown,
yellow, blue and green. The beverages are tea, juice, offfee and
water.

1. The Yellow house is immediately to the right of the green house and
has only
one neighbor.
2. Milk is drunk in the middle house.
3. The man in the red house lives immediately to the left of theman
who smokes
Chesterfields.
4. The Norweigian doesn't drink milk.
5. Tea is drunk in the house next to where the Scotsmnan lives.
6. The Scotsman smokes Camels.
7. The Irishman lives immediately to the left of theman who smokes
Pall Malls.
8. The dog lives in the house next to the house where the Irishman
lives.
9. Ther German lives next to the blue house.
10 The Germandrinks coffee.
11. The Giraffe and the elephant lives in houses separated by the blue
house.
12. The man in the green house smokes Salems.
13. The Englishman lives to the right of the house where juice is
drunk.
14. The horse lives in the niddle house.
15. The camel lives next tot he house where Kools are smoked.
16. The Scotsman lives in the brown house.

Now, who drinks tea? Who owns the horse? Name all five things for all
five homes.


Good Luck
Jaz


--
jazbath
------------------------------------------------------------------------
jazbath's Profile:
http://www.excelforum.com/member.php...o&userid=29467
View this thread: http://www.excelforum.com/showthread...hreadid=491735

Hi Dana,
It seems very interesting puzzle. But I did not understand the method
you are talking about. Would love to learn more. I would certainly
like to know if someone else have already solved it on paper.
regards
Syed


--
saziz
------------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=491735

Here is one solution using formulas and no programming
H1 H2 H3 H4 H5
Nat G N S I E
Cig K Ch Ca S P
Col R Bu Br G Y
Bev C T M J W
Ani G C H E D

Statements 1,2,12 and 14 have obvious answers and 5 squares
can be filled in immediately.
For the 12 remaining statements, make a 12 x 5 array as shown
1 2 3 4 5
Q10 1 0 0 0 0
Q4 0 1 0 0 0
Q7 0 0 0 1
Q6 0 0 1 0 0
Q3 1 0 0 0
Q16 0 0 1 0 0
Q15 0 1 0 0 0
Q5 0 0 1 0 0
Q8 0 0 0 1 0
Q9 1 0 0 0 0
Q13 0 0 0 1
Q11 1 0 0

Translate the statements into logic and enter into the array.
Statement 10 (Q10) translates into
=(Nat="G")*(Bev="C") for all houses
Statement 15 (Q15) translates into
=(Ani="C")*OR(Cig C[1]="K",Cig C[-1]="K") for houses 2, 3 and 4
Continue with the rest of the statements. Read statements carefully.
Add conditional formatting to this array to highlight cells that cannot
be true with the given values found so far.
You can do this by inspection or automate it by creating an error array
and linking the conditional formatting to it.
A portion of the 26 x 5 error array is shown here
con1 res1 con2 off2 res2
Q10 0 1 1 1 1 Nat G Bev C C
Q4.1 1 0 1 1 1 Nat N Bev C T
Q4.2 1 1 1 1 1 Nat N Bev C J
Q4.3 1 1 1 1 1 Nat N Bev C C
Q4.4 1 1 1 1 1 Nat N Bev C W
Q7 1 1 1 0 1 Nat I Cig C[1] P
Q6 1 1 0 1 1 Nat S Cig C Ca
Q3 0 1 1 1 1 Col R Cig C[1] Ch
Q16 1 1 0 1 1 Nat S Col C Br
Q15.1 1 1 1 1 1 Ani C Cig C[1] K
Q15.2 1 0 1 1 1 Ani C Cig C[-1] K

Fill the 26 x 5 array (the 1s and 0s) with this formula
=--OR(AND(COUNTIF(INDIRECT(con1),res1)=1,NOT(INDIRECT (con1)=res1)),
AND(NOT(INDIRECT(con1)=res1),NOT(ISBLANK(INDIRECT( con1)))),
AND(COUNTIF(INDIRECT(con2),res2)=1,
NOT((INDIRECT(con2,0) INDIRECT(off2,0))=res2)),
AND(NOT((INDIRECT(con2,0) INDIRECT(off2,0))=res2),
NOT(ISBLANK((INDIRECT(con2,0) INDIRECT(off2,0))))))

The array to the right is a subset of the equations in the 12 x 5
array,
arranged somewhat differently for ease of data entry.
Make lists of the values still available for each house and inspect the
equations in the 12 x 5 array so addittional cells can be flagged as
errors.
Eventually you will find a single cell in a row that has not been
flagged.
This cell contains valid data that can be transferred to the 5 x 5
array.
More error cells will highlight and everything will fall into place.
Everything R1C1 of course.

Hi. I may be wrong, but my program keeps telling me that there are no
solutions. It doesn't like clue # 14. Are you sure this is correct?
(14. The horse lives in the middle house.)

If I remove clue #14, I get two solutions. The only difference that I show
is only a giraffe or a elephant going into house #3 (middle house).

1,German,red,coffee,kools,giraffe,
2,Norwegian,blue,tea,chesterfields,camel,
3,Scotsman,brown,milk,camels,elephant,
4,Irishman,green,juice,salems,horse,
5,Englishman,yellow,water,pallmalls,dog,

1,German,red,coffee,kools,elephant,
2,Norwegian,blue,tea,chesterfields,camel,
3,Scotsman,brown,milk,camels,giraffe,
4,Irishman,green,juice,salems,horse,
5,Englishman,yellow,water,pallmalls,dog

Good luck. :)
--
Dana DeLouis
Win XP & Office 2003


"saziz" wrote in
message ...

Hi Dana,
It seems very interesting puzzle. But I did not understand the method
you are talking about. Would love to learn more. I would certainly
like to know if someone else have already solved it on paper.
regards
Syed


--
saziz
------------------------------------------------------------------------
saziz's Profile:
http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=491735

Hi, Dana:

I had trouble with these rules, too. My problem was with #11, i.e. "The
Giraffe and the elephant live in houses separated by the blue house."

If I interpret that as "The Giraffe and the elephant live in houses separated
ONLY by the blue house", I can't find a solution, either.

OTOH, if I interpret it to mean that can be ONE OR MORE houses between the
giraffe and elephant, and one of them is blue, I also came up with 2
solutions.

#1 is the same as Herbert's. #2 is identical to #1 except that (like your
solutions) the giraffe and elephant are interchanged.

But I didn't do any of it with formulas or programming. I did it manually.

On Sat, 10 Dec 2005 04:58:01 -0500, "Dana DeLouis"
wrote:

Hi. I may be wrong, but my program keeps telling me that there are no
solutions. It doesn't like clue # 14. Are you sure this is correct?
(14. The horse lives in the middle house.)

If I remove clue #14, I get two solutions. The only difference that I show
is only a giraffe or a elephant going into house #3 (middle house).

1,German,red,coffee,kools,giraffe,
2,Norwegian,blue,tea,chesterfields,camel,
3,Scotsman,brown,milk,camels,elephant,
4,Irishman,green,juice,salems,horse,
5,Englishman,yellow,water,pallmalls,dog,

1,German,red,coffee,kools,elephant,
2,Norwegian,blue,tea,chesterfields,camel,
3,Scotsman,brown,milk,camels,giraffe,
4,Irishman,green,juice,salems,horse,
5,Englishman,yellow,water,pallmalls,dog

Good luck. :)

OTOH, if I interpret it to mean that can be ONE OR MORE houses between the
giraffe and elephant, and one of them is blue, I also came up with 2
solutions.

Ahhh! Thank you Myrna. You are absolutely correct.
If I change that line in my program, I also get two solutions.
Thanks. :)

--
Dana DeLouis
Win XP & Office 2003


"Myrna Larson" wrote in message
...
Hi, Dana:

I had trouble with these rules, too. My problem was with #11, i.e. "The
Giraffe and the elephant live in houses separated by the blue house."

If I interpret that as "The Giraffe and the elephant live in houses
separated
ONLY by the blue house", I can't find a solution, either.

OTOH, if I interpret it to mean that can be ONE OR MORE houses between the
giraffe and elephant, and one of them is blue, I also came up with 2
solutions.

#1 is the same as Herbert's. #2 is identical to #1 except that (like your
solutions) the giraffe and elephant are interchanged.

But I didn't do any of it with formulas or programming. I did it manually.

On Sat, 10 Dec 2005 04:58:01 -0500, "Dana DeLouis"
wrote:

Hi. I may be wrong, but my program keeps telling me that there are no
solutions. It doesn't like clue # 14. Are you sure this is correct?
(14. The horse lives in the middle house.)

If I remove clue #14, I get two solutions. The only difference that I
show
is only a giraffe or a elephant going into house #3 (middle house).

1,German,red,coffee,kools,giraffe,
2,Norwegian,blue,tea,chesterfields,camel,
3,Scotsman,brown,milk,camels,elephant,
4,Irishman,green,juice,salems,horse,
5,Englishman,yellow,water,pallmalls,dog,

1,German,red,coffee,kools,elephant,
2,Norwegian,blue,tea,chesterfields,camel,
3,Scotsman,brown,milk,camels,giraffe,
4,Irishman,green,juice,salems,horse,
5,Englishman,yellow,water,pallmalls,dog

Good luck. :)

Since posting my original message, I wrote a macro to do it, and came up with
the same 2 solutions I had discovered manually (which was reassuring!). Of
course the macro found the solutions in less than 1 second (not counting
programming time) vs an hour or so that I spent doing it the "old fashioned"
way.

Now that we've spent all of this time on it, the OP will probably come back
and tell us that one or more of the other conditions were stated incorrectly
:(. But we have the macros written, so making a change will be trivial.

Actually, I expect the problem is supposed to have only one solution, which
means there is an error somewhere in the "specs".

Has the OP been heard from since, or are we just talking to ourselves?

On Sun, 11 Dec 2005 21:17:56 -0500, "Dana DeLouis"
wrote:

OTOH, if I interpret it to mean there are ONE OR MORE houses between the
giraffe and elephant, and one of them is blue, I also came up with 2
solutions.

Ahhh! Thank you Myrna. You are absolutely correct.
If I change that line in my program, I also get two solutions.
Thanks. :)

PS: using Google to look for Norwegian and camel and juice and Kool, I found
that this seems to be a variant of a "standard" programming problem called the
Zebra Puzzle. That puzzle, which specifies a different set of rules, has only
one solution.

On Sun, 11 Dec 2005 23:00:51 -0600, Myrna Larson
wrote:

Since posting my original message, I wrote a macro to do it, and came up with
the same 2 solutions I had discovered manually (which was reassuring!). Of
course the macro found the solutions in less than 1 second (not counting
programming time) vs an hour or so that I spent doing it the "old fashioned"
way.

Now that we've spent all of this time on it, the OP will probably come back
and tell us that one or more of the other conditions were stated incorrectly
:(. But we have the macros written, so making a change will be trivial.

Actually, I expect the problem is supposed to have only one solution, which
means there is an error somewhere in the "specs".

Has the OP been heard from since, or are we just talking to ourselves?

On Sun, 11 Dec 2005 21:17:56 -0500, "Dana DeLouis"
wrote:

OTOH, if I interpret it to mean there are ONE OR MORE houses between the
giraffe and elephant, and one of them is blue, I also came up with 2
solutions.

Ahhh! Thank you Myrna. You are absolutely correct.
If I change that line in my program, I also get two solutions.
Thanks. :)