Discrete Mathematics Practical 7
Page 1 of 2 Oliver Hyde
Practical 7: Caesar Cipher revisited
Requirements
Create a spreadsheet in Excel that will decode up to two lines of a Caesar Cipher into
plaintext (i.e. plain English). A template based on our previous Caesar Cipher spreadsheet
from Practical 5, called Caesar Cipher template.xlsx, is available on the Moodle page for
the module.
The user is required to:
• Enter the Caesar Cipher into the spreadsheet.
• Enter the shift key1 so that the spreadsheet can convert the cipher into plaintext.
• Protect the spreadsheet so that the user can only change the contents of the yellow
(user input) cells.
Important Excel functions:
• CODE function: returns the ASCII code for a given character.
• CHAR function: returns the character for a given ASCII code.
Table showing ASCII codes for capital letters.
Character A B C D E F G H I J K L M N O …
ASCII code XXXXXXXXXX XXXXXXXXXX 78 79 …
Sample Caesar Cipher on the paranormal to test your completed spreadsheet (shift key = 5):
YMTXJ BMT GJQNJAJ NS YJQJPNSJXNX
WFNXJ RD MFSI
1 Plaintext to Ciphertext: Each plaintext letter is shifted to the right (in the alphabet) by a number of positions
equal to the shift key, wrapping around as necessary after the letter Z.
Discrete Mathematics Practical 7
Page 2 of 2 Oliver Hyde
Algorithm for finding shifted Plaintext
IF
( Logical test: ASCII code of ciphertext – shift key < 65,
Value if true: Display as a character ( ASCII code of ciphertext – shift key + 26 ),
Value if false: Display as a character ( ASCII code of ciphertext – shift key )
)
Notes:
• The cell reference to the shift key must be fixed in position so that the formula can be
copied to all 26 letters in the alphabet. Use the F4 button to set an absolute cell
eference.
• Capital A = ASCII code 65.
• The Value if false: code performs the “wrap around” part of finding the shifted
Plaintext.
Protecting your Spreadsheet
1. All user input cells (in yellow) must have their protection set to Unlocked. To do this,
select one or more cells to be unlocked, then right-click on mouse and choose Format
Cells… from the drop-down menu. Click on the Protection tab, and untick the Locked
ox. Repeat as necessary until all user input cells are unlocked.
2. From the ri
on/menu bar, click on the Review tab, followed by Protect Sheet. Untick
the “Select locked cells” option, then click OK. Do not set a password for this
spreadsheet as your lecturer will be unable to examine your code and mark your work.
3. From the ri
on/menu bar, click on the Review tab, followed by Unprotect Sheet,
then OK if you want to “unprotect” a protected sheet.
Submitting your Answers
❑ Create a single Excel spreadsheet named as follows: the word Practical, followed by a
space and the practical number, i..e. Practical 7.
❑ Submit the spreadsheet via Moodle by Friday before 11:55 p.m. You may only submit
once. Late submissions are not permitted.