Encryption and Decryption Using the Vigenere Cipher The Vigenere Table has been
ID: 3802695 • Letter: E
Question
Encryption and Decryption Using the Vigenere Cipher
The Vigenere Table has been entered in cells A2 thru Z27 of the Lab3 spreadsheet.
Encryption
1. Enter the encryption/decryption key (up to 7 letters) beginning in cell AB3 of the spreadsheet.
2. Enter the plaintext message (up to 7 characters) beginning in cell AB8 of the spreadsheet.
3. In cells A30 thru B55, create a table to translate each letter from the key to a column number (A=1, B=2, etc.).
4.Calculate the ciphertext values. Enter a formula in Cell AB10, that will look up the plaintext character in column 1 of the Vigenere table, and find its corresponding ciphertext character in the column number calculated in Step 3. Drag the formula across to get the remaining ciphertext values.
Decryption
5.Write a formula that uses the translation table in cells A30 thru B55 to calculate a translation factor for each letter of the key. (Note: this number starts at 1 not 0, as we have used in all class examples; this is because we used the number to identify the correct column to use to encrypt.) Enter the formula in cells AB20 to AH20.
6.Use translation table in cells AC30 to AD55 to convert each step 6 result from a numeric to a letter, using A=0, B=1,etc. to find the plaintext. Since the Vigenere Tableau was created by adding a translation factor to each character value, the decryption can be achieved by subtracting the translation factor, MOD 26.
VIGENERE TABLE ENCRYPTION a b c d e f g h i j k l m n o p q r s t u v w x y z ENTER THE KEY (up to 7 letters) in CELLS AB3 thru AH3: b c d e f g h i j k l m n o p q r s t u v w x y z a r i l e y t c d e f g h i j k l m n o p q r s t u v w x y z a b d e f g h i j k l m n o p q r s t u v w x y z a b c e f g h i j k l m n o p q r s t u v w x y z a b c d ENTER THE PLAINTEXT (UP TO 7 LETTERS) in CELLS AB8 thru AH8: f g h i j k l m n o p q r s t u v w x y z a b c d e PLAINTEXT: g h i j k l m n o p q r s t u v w x y z a b c d e f h e l l o h i j k l m n o p q r s t u v w x y z a b c d e f g CIPHERTEXT: i j k l m n o p q r s t u v w x y z a b c d e f g h j k l m n o p q r s t u v w x y z a b c d e f g h i k l m n o p q r s t u v w x y z a b c d e f g h i j l m n o p q r s t u v w x y z a b c d e f g h i j k m n o p q r s t u v w x y z a b c d e f g h i j k l DECRYPTION n o p q r s t u v w x y z a b c d e f g h i j k l m ENTER THE CIPHERTEXT (UP TO 7 LETTERS) IN CELLS AB17 THRU AH17 o p q r s t u v w x y z a b c d e f g h i j k l m n CIPHERTEXT p q r s t u v w x y z a b c d e f g h i j k l m n o y m w p m q r s t u v w x y z a b c d e f g h i j k l m n o p r s t u v w x y z a b c d e f g h i j k l m n o p q Translation Factor s t u v w x y z a b c d e f g h i j k l m n o p q r t u v w x y z a b c d e f g h i j k l m n o p q r s u v w x y z a b c d e f g h i j k l m n o p q r s t PLAINTEXT v w x y z a b c d e f g h i j k l m n o p q r s t u w x y z a b c d e f g h i j k l m n o p q r s t u v x y z a b c d e f g h i j k l m n o p q r s t u v w y z a b c d e f g h i j k l m n o p q r s t u v w x z a b c d e f g h i j k l m n o p q r s t u v w x y Translate Key Letter to a Column Number Translation Table a 1 0 a b 2 1 b c 3 2 c d 4 3 d e 5 4 e f 6 5 f g 7 6 g h 8 7 h i 9 8 i j 10 9 j k 11 10 k l 12 11 l m 13 12 m n 14 13 n o 15 14 o p 16 15 p q 17 16 q r 18 17 r s 19 18 s t 20 19 t u 21 20 u v 22 21 v w 23 22 w x 24 23 x y 25 24 y z 26 25 zExplanation / Answer
Below are the formulas used in the calculation of the cipher text and decoding the cipher text into the plain text:
1: To get the ci[her text in the first part (encryption) to be used in the cell AB10: VLOOKUP(AB8, A2:Z27,LOOKUP(AB8, A30:A55, B30:B55),FALSE)
2: To get the translation factor, use the following formula in the step 5: LOOKUP(AB3,A30:A55,B30:B55)
3: TO get the plain text: use the below formula (Step6): VLOOKUP(LOOKUP(AB3,A30:A55,B30:B55),A30:A55,30,FALSE)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.