by **spnotation** » Sat Nov 02, 2013 4:09 am

The puzzle solver is quite colorful and I'm just glad that the colors aren't too bright. I've seen spreadsheets that cause quite an eyestrain from bright and clashing colors.

You should blank out the candidates (possible values) if the cell is filled. You can do this by adding a not(isblank(xx)) condition. An example would be the formula for H14 (candidate "4") for cell K14

=IF(OR(NOT(ISBLANK(K14)),K2=4,K6=4,K10=4,K14=4,K18=4,K22=4,K26=4,K30=4,K34=4,P14=4,U14=4,Z14=4,AE14=4,AJ14=4,AO14=4,AT14=4,F14=4,P18=4,P22=4,F18=4,F22=4),"",4)

Actually, the condition K14=4 above is redundant, but it is easier to retain it to make the formula easier to copy.

Here is another way to do this:

= if(and(iserror(find("4", concatenate(K2,K6,K10,K14,K18, .... ,F22)), not(isblank(K14))), 4,"")

The concatenate() function converts numbers to text and blanks to "", which makes the find() function work like the OR function. It just makes it more elegant to work with a list of cells instead of a series of = conditions.

-------------------------------

The vertical merged cells should use the count() function instead of the sum() of 0s and 1s in the blocks below. E2 should be =count(b2:d4) instead of =sum(b47:c49). You don't need those cells of 0s and 1s and you can use the space for other things ....

--------------------------------

The vertical and horizontal merged cells provides helps with rows and columns, but not on the 3x3 blocks. To do the block helper, you can add another band, just like b39:at44, for the 3x3 blocks.

The B39 formula =COUNTIF(B2:B36,"=1") will be the pattern, such that

B47 formula = COUNTIF(B2:D12,"=1") + COUNTIF(G2:I12,"=1") + COUNTIF(L2:N12,"=1")

-------------------------------

You can add a feature to highlight the remaining possible locations of a number. If I want to analyze 8, I can type in 8 in A1 and this will cause all 8 possibilities to change color.

-------------------------------

I use certain techniques to eliminate candidates. It would be useful if possibilities can be "eliminated" by deletion or by "x" and restored by copying an adjacent cell. It would take a lot of work to make the formulas more "copyable". Absolute cell references like $B$2:$D$12 will be needed, as well as formulas for the candidate numbers 1 to 9, by using the mod(), row() and column() functions.

As it is, the only way to restore the candidate cells back to the original formula would be to copy them from a master worksheet.

You can find the elimination techniques in my 2011 blog: spnotation.blogspot.ca. I used A1 notation for cell references, just like Excel, since I started out with an Excel solver like you.

--------------------------------