View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
Herbert Seidenberg
 
Posts: n/a
Default Critical thinking puzzle

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.