Solution to Hey, Can You Give Me A Hand With This Puzzle?
by Colin Lu, Jakob Weisblat, Leon Zhou, Lillian McKinley
This puzzle consists of a Google Sheet with ten sheets which appear to correspond to hypothetical “puzzles”, and finally a sheet at the end called “Extract”.
Each of the puzzle sheets has a crucial step remaining in order to extract the answer for that puzzle and includes notes that suggest what specifically needs to be done. In each case, there is a shaded cell, as well as a solid border around some cells in the sheet that need to be filled. By filling the highlighted cell with the appropriate formula, the correct data can be placed in all of the color-boxed cells by simply copying that formula.
For the first few sheets, the required formulas are quite simple, but the later sheets require some lesser-known formulas.
Possible solutions follow for the highlighted cell in each of the sheets:
|Crossword Chaos||=MID(B2,Q2,1)||MID takes a chunk of a word, so it’s good for indexing.||CROSSWORDCHAOSCROSSWORDCHAOSCR|
|Mystery Hunt History||=CHAR(D6-1900)||CHAR converts a numberical value to its ASCII character equivalent.||ANSWERTOTHEPUZZLE|
|Identity Crisis||=CHAR(D5+64)||CHAR can also be used for "A1Z26" substitutions.||IGNORETHESEANSWERSPLEASE|
|Programming Problems||=UPPER(MID(REGEXREPLACE(A5,"[^A-Za-z]",""),P5,1))||This one is tricky. We use a regular expression to substitute out all the non-alphabetic characters before using the indexing trick from above.||SUCCESSYOUSOLVEDITGREATGREAT|
|Countries||=INDEX($E:$E,MATCH(INDEX($B:$B,MATCH(H5,$A:$A,0)),$D:$D,0))||INDEX and MATCH are crucial functions for solving puzzles in google sheets. They’re very versatile, and you can use them to look up data in tables of any direction or shape. You can also see dollar signs used here to lock in place some of the references.||CANONICALLISTICLE|
|Data Disentanglement||=INDIRECT(AF3&B3)||INDIRECT is another fun function, that converts a cell title as a string to an actual reference. You can also see “&” which is shorthand for concatenation here. INDIRECT sometimes works well with ADDRESS, which makes a cell name string.||MECHANICALSEAMONSTER|
|Vowels and Consonants||=CHAR(BIN2DEC(C7)+64)||BIN2DEC is a little funny in that it only likes numbers smaller than 512.||GREATJOBYOUDIDIT|
|Frequency Analysis||=CHAR(LEN($A$3)-LEN(SUBSTITUTE($A$3,A11,""))+64)||There’s a bunch of ways to do this one. This is probably the simplest though. It’s a little clever - the number of occurences of a letter in a string is the length of the string minus the length of the string with that character removed.||CONGRATULATIONSYOUSOLVEDIT|
|Pokemon Sudoku||=ARRAYFORMULA(CONCATENATE(IF($EJ$12:$EL$14=A12,MID($EJ$12:$EL$14,$EG$8:$EI$10,1),"")))||This is the hardest subpuzzle. This solution abuses CONCATENATE and ARRAYFORMULA to try a bunch of things at once and use only the one that worked. Generally ARRAYFORMULA is an extremely useful function but it can be hard to think about at times. It makes any function that would operate on a single cell operate across an array of cells instead, applying to each one.||BRILLIANT|
|Too many numbers!||=CHAR(COUNTIF($EE$276:$FR$529,A277)-936)||COUNTIF is another standard useful function. It can also count things that are greater than a given number, or a number of other ways of counting. Dollar signs are almost always necessary with COUNTIF if you’re using it to count different things in the same range.||WOWNICEONE|
In some cases, there are some other reasonable formulas that would also work, such as using many instances of SUBSTITUTE instead of one REGEXREPLACE for Programming Problems.
As emphasised by the first sheet, and the answer to Identity Crisis, the “answers” on each sheet are not relevant for solving the puzzle; they are just placeholders so that solvers can verify that their solution for each sheet is correct.
As hinted on the final “Extract” sheet, we proceed by copying the formulas from the previous sheets into the corresponding colored boxes on the final sheet. Note that in Google Sheets this can be done by either directly copying the formula text from the cell, or by copying the cell itself. The latter is correct here, as hinted by the large number of hidden columns in the previous sheets, which would not be relevant in the former case, and by the comment above the formula pasting area.
When all of the formulas are pasted into the extraction sheet, the conditional formatting produces the following image:
This depicts James Bond. The four blanks at the bottom clue our four-letter answer, BOND.