this assignment has to be done in Microsoft excel
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 65 66 67 68 69 70 71 72 73 74 75 76 77 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 reference. • 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 box. repeat as necessary until all user input cells are unlocked. 2. from the ribbon/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 ribbon/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. 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="" reference.="" •="" 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="" box.="" repeat="" as="" necessary="" until="" all="" user="" input="" cells="" are="" unlocked.="" 2.="" from="" the="" ribbon/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="" ribbon/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="">