Encrypting a letter with a Caesar formula in a spreadsheet
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which formula correctly encrypts the capital letter in A2 using the key in B1, so it can be copied across?
2. What does CODE(A2) return if A2 contains the letter C?
3. Why does the formula subtract 65 from CODE(A2)?
4. What is the job of MOD(...,26) in the Caesar formula?
5. The key in B1 is 3 and A2 contains X. Which letter does the formula return?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1=CHAR(MOD(CODE(A2)-65+$B$1,26)+65) | AShifts the capital letter in A2 forward by the key in $B$1 and returns the encrypted letter | |
| 2$B$1 | BThe +65 is missing at the end, so CHAR gets a number from 0 to 25 instead of a letter code | |
| 3Subtracting 65 turns a capital letter's code into a position from 0 to 25 (A = 0) | CMOD(CODE(A2)-65+$B$1,26) | |
| 4MOD with 26 wraps a shifted position back to the start of the alphabet so Z plus 1 becomes A | DCODE(A2)-65 | |
| 5=CHAR(MOD(CODE(A2)-65+B1,26)+65) | EThe key B1 is not locked, so when the formula is copied across it becomes C1, D1 and so on and the shift changes | |
| 6=CHAR(MOD(CODE(A2)-65+$B$1,26)) | FA locked cell reference that keeps pointing at the key when the formula is copied |
C
Definitions
=CHAR(MOD(CODE(A2)-65+$B$1,26)+65)
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=____(MOD(CODE(A2)-65+$B$1,26)+65)
2.
=CHAR(MOD(____(A2)-65+$B$1,26)+65)
3.
=CHAR(MOD(CODE(A2)-65+____,26)+65)
4.
=CHAR(MOD(CODE(A2)-65+$B$1,__)+65)
5.
=CHAR(MOD(CODE(__)-65+$B$1,26)+65)
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
=CHAR(MOD(
B
CODE(A2)-65+$B$1,
C
25)+65)
Question 2
A
C2: =CHAR(A2)-65
B
D2: =MOD(C2+$B$1,26)
C
E2: =CHAR(D2+65)
Question 3
A
=CHAR(MOD(
B
CODE(A2)-65+$B$1,
C
26)
D
+64)
Question 4
A
B1: three
B
A2: H
C
B2: =CODE(A2)-65+$B$1
Question 5
A
=CHAR(
B
MOD(CODE(A2)-65+$B$1,26)
C
-65)
Answer Key
Encrypting a letter with a Caesar formula in a spreadsheet
Section A: Quick Questions
Q1
B — =CHAR(MOD(CODE(A2)-65+$B$1,26)+65)
Q2
A — 67
Q3
B — So A becomes 0, B becomes 1 and Z becomes 25
Q4
A — It wraps a shift past Z back around to A
Q5
A — A
Section B: Match the Pairs
Q1
=CHAR(MOD(CODE(A2)-65+$B$1,26)+65) → Shifts the capital letter in A2 forward by the key in $B$1 and returns the encrypted letter
Q2
$B$1 → A locked cell reference that keeps pointing at the key when the formula is copied
Q3
Subtracting 65 turns a capital letter's code into a position from 0 to 25 (A = 0) → CODE(A2)-65
Q4
MOD with 26 wraps a shifted position back to the start of the alphabet so Z plus 1 becomes A → MOD(CODE(A2)-65+$B$1,26)
Q5
=CHAR(MOD(CODE(A2)-65+B1,26)+65) → The key B1 is not locked, so when the formula is copied across it becomes C1, D1 and so on and the shift changes
Q6
=CHAR(MOD(CODE(A2)-65+$B$1,26)) → The +65 is missing at the end, so CHAR gets a number from 0 to 25 instead of a letter code
Section C: Definitions
Q1
=CHAR(MOD(CODE(A2)-65+$B$1,26)+65) — Shifts the capital letter in A2 forward by the key in locked cell $B$1, wrapping Z back to A, and returns the encrypted letter. Copy it across so each cell encrypts one letter.Section D: Fill in the Blanks
Q1
=CHAR(MOD(CODE(A2)-65+$B$1,26)+65)Q2
=CHAR(MOD(CODE(A2)-65+$B$1,26)+65)Q3
=CHAR(MOD(CODE(A2)-65+$B$1,26)+65)Q4
=CHAR(MOD(CODE(A2)-65+$B$1,26)+65)Q5
=CHAR(MOD(CODE(B2)-65+$B$1,26)+65)Section E: Spot the Error
Q1
C — The alphabet has 26 letters, so MOD must divide by 26 to wrap Z back to A. Using 25 gives the wrong letters. It should be 26)+65).
Q2
A — CHAR turns a number into a letter, but A2 holds a letter, so CODE is needed to get its number first. It should be C2: =CODE(A2)-65.
Q3
D — After MOD gives a number from 0 to 25, you add 65 to turn it back into a capital-letter code. Adding 64 makes every letter one step too early. It should be +65).
Q4
A — The key must be a number so the formula can add it to the letter's number. The word three would cause a #VALUE! error. It should be B1: 3.
Q5
C — The 65 was subtracted at the start to get a number from 0 to 25. It must be added back at the end so CHAR gets a capital-letter code. It should be +65).