Killer Sudoku Pro: spreadsheet helper

Challenge: There is a variant of Sudoku called Killer Sudoku in which areas of the grid, either square, rectangular or some other irregular shape, are caged off. A small number in the corner indicates the value to which the numbers therein must sum (or multiply, or subtract, or divide). Produce a spreadsheet that identifies all possible combinations for three-box cages.

Here's the problem in a bit more depth. There is an indicator in the corner to confirm which of the four mathematical operators to use on the cage. The challenge was to take the leg-work out of figuring out the possible combinations.

The division operator is trivial, so didn't need to be included in the challenge. It only operates on two-box cells, and all combinations can easily be accommodated mentally. So we were left with multiplication, addition and subtraction.

The logic is on the Workings sheet of the attached spreadsheet..

Columns M, N and O contain all 165 three-way combinations of the numbers 1 through 9. Columns A through D deal with the subtraction option; E through H with multiplication; and I through L with addition.

For each row, working right to left, the result is simply the result of the calculation. It's easy for multiplication (PRODUCT) and addition (SUM).

For subtraction it's a bit more tricky. The subtraction rule dictates that you subtract the two smaller numbers from the biggest number. But beautifully, this is the same as =2*MAX(range)-SUM(range). By taking twice the maximum number and subtracting the sum of the three, it's the same as taking the maximum number and subtracting the two smaller numbers.

On the "Answer" sheet, you simply enter the number from the Sudoku puzzle (the one that you're trying to evaluate against) and select which type of cage it is: multiply, sum or minus.

Back on the "Workings" sheet, the "Match?" column simply compares the answer to the value you're evaluating against. It returns a 1 for a match, and a 0 for a non-match.

The sequence column then adds the previous column to give the sequence number of the entry. And the "Unique sequence" column merely strips out the duplicates from the previous column, tagging just the matching rows with the relevant sequence number.

Cell L2 on the "Answers" sheet contains a formula detailing how many exact matches there are. By using a format of ";;;", the cell contains the value but will never display it.

Column E contains the unique reference of each successful match, using L2 as the basis for determining when to stop. Columns F, G and H simply use a VLOOKUP to bring back the relevant values from the "Workings" sheet.

• Tweet