| Lab 6 | |||||||||
| Example 2.5.3 of Thomas Barr | |||||||||
| =COUNTIF(B$4:B$24,$F4) | |||||||||
| C | T | M | A | 0 | 0 | 0 | |||
| Y | R | D | B | 1 | 0 | 1 | |||
| O | I | B | C | 2 | 0 | 2 | |||
| S | R | E | D | 2 | 0 | 1 | |||
| S | R | R | E | 0 | 0 | 2 | |||
| R | I | J | F | 0 | 1 | 2 | |||
| Y | R | E | G | 0 | 0 | 0 | |||
| B | Y | L | H | 0 | 0 | 0 | |||
| D | I | Y | I | 0 | 4 | 0 | |||
| M | L | C | J | 0 | 1 | 1 | |||
| C | Y | Q | K | 1 | 0 | 0 | |||
| X | S | R | L | 0 | 1 | 2 | |||
| R | M | L | M | 1 | 1 | 2 | |||
| Q | F | S | N | 0 | 0 | 0 | |||
| D | X | F | O | 2 | 0 | 0 | |||
| O | W | F | P | 0 | 0 | 0 | |||
| K | T | C | Q | 1 | 0 | 2 | |||
| Y | J | R | R | 3 | 4 | 3 | |||
| R | I | Q | S | 2 | 2 | 1 | |||
| Z | S | M | T | 0 | 2 | 0 | |||
| X | U | 0 | 0 | 0 | |||||
| V | 0 | 0 | 0 | ||||||
| W | 0 | 1 | 0 | ||||||
| X | 2 | 1 | 0 | ||||||
| Y | 3 | 2 | 1 | ||||||
| Z | 1 | 0 | 0 | ||||||
| In G30: | =sum(G4:G29) | N | 21 | 20 | 20 | ||||
| I.C. | 0.052381 | 0.078947 | 0.047368 | ||||||
| In G31: | =(sumsq(G4:G29)-sum(G4:G29))/(G30*(G30-1)) | ||||||||
| WHAT | I copied the crypt from Barr's example 2.5.3 | ||||||||
| I DID | CTMYR DOIBS RESRR RIJYR EBYLD IYMLC CYQXS | ||||||||
| RRMLQ FSDXF OWFKT CYJRR IQZSM X | |||||||||
| into the three columns B:D, rows 4:24 above. | |||||||||
| I entered the alphabet in column F, rows 4:29. | |||||||||
| In cell G4 I entered the formula | |||||||||
| =COUNTIF(B$4:B$24,$F4) | |||||||||
| Note carefully the $ signs; they precede *constant* values. | |||||||||
| I copied this formula into the G4:I29 rectangle. | |||||||||
| Thus, I got a count of the letters in each column. | |||||||||
| I then calculated the number of letters in each column and the index of coincidence: | |||||||||
| In G30: | =sum(G4:G29) | ||||||||
| In G31: | =(sumsq(G4:G29)-sum(G4:G29))/(G30*(G30-1)) | ||||||||
| I copied these into the cells to the right to complete the calculations. | |||||||||
| You may be able to see that "KEY"="aaa" and thus decipher the crypt: | |||||||||
| Spoon feeding in the long run teaches us nothing but the shape of the spoon. | |||||||||
| DO THIS: | Barr 2.5 #5: Mimic the above for the following crypt, | ||||||||
| which was Vigenere enciphered enciphered with a three-letter English keyword: | |||||||||
| RLWRV MRLAQ EDUEQ QWGKI LFMFE XZYXA QXGJH FMXKM QWRLA | |||||||||
| LKLFE LGWCL SOLMX RLWPI OCVWL SKNIS IMFES JUVAR MFEXZ | |||||||||
| CVWUS MJHTC RGRVM RLSZS MREFW XZGRY RLWPI OMYDB SFJCT | |||||||||
| CAZYX AQ | |||||||||
| EXTRA | Solve the crypt. | ||||||||
| CREDIT | |||||||||