The MOD function in a spreadsheet
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. What does =MOD(27,26) return?
2. Which formula wraps a letter position of 27 back into the range 1 to 26?
3. What does =MOD(10,3) return?
4. Letters are numbered A=0 to Z=25. What does =MOD(25+3,26) return for a shift of 3 on Z?
5. What does =MOD(26,26) 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=MOD(27,26) | AGives 23, so a negative number wraps backwards into the range 0 to 25 | |
| 2=MOD(-3,26) | B=MOD(B2+3,26) | |
| 3Shift a letter position in B2 forward by 3 and wrap it back into 0 to 25 | CThe divisor is 25, but the alphabet has 26 letters, so the wrap-around happens at the wrong place. | |
| 4Decrypt by shifting a letter position in B2 back by 3 without going below 0 | DThe number and the divisor are the wrong way round. The divisor 26 should come second and the shifted position first. | |
| 5=MOD(26,B2+3) | EGives 1, because 27 divided by 26 leaves a remainder of 1 | |
| 6=MOD(B2+3,25) | F=MOD(B2-3,26) |
C
Definitions
=MOD(number, divisor)
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=___(27,26)
2.
=MOD(27,__)
3.
=MOD(__,26)
4.
=MOD(__+3,26)
5.
=MOD(A2_3,26)
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
A2: 25
B
B2: 3
C
C2: =A2+B2
D
D2: =MOD(26,C2)
Question 2
A
A1: 27
B
B1: =MODULO(A1,26)
C
C1: result is 1
Question 3
A
A1: 0
B
B1: =A1-3
C
C1: =MOD(B1,-26)
D
D1: result is 23
Question 4
A
A1: 30
B
B1: =MOD(A1,26)
C
C1: =CODE(B1+65)
D
C1 shows E
Question 5
A
B1: =MOD(26,27)
B
B1 shows 1
C
B2: =MOD(52,26)
D
B2 shows 0
Answer Key
The MOD function in a spreadsheet
Section A: Quick Questions
Q1
A — 1
Q2
B — =MOD(27,26)
Q3
B — 1
Q4
A — 2, which is the letter C
Q5
A — 0
Section B: Match the Pairs
Q1
=MOD(27,26) → Gives 1, because 27 divided by 26 leaves a remainder of 1
Q2
=MOD(-3,26) → Gives 23, so a negative number wraps backwards into the range 0 to 25
Q3
Shift a letter position in B2 forward by 3 and wrap it back into 0 to 25 → =MOD(B2+3,26)
Q4
Decrypt by shifting a letter position in B2 back by 3 without going below 0 → =MOD(B2-3,26)
Q5
=MOD(26,B2+3) → The number and the divisor are the wrong way round. The divisor 26 should come second and the shifted position first.
Q6
=MOD(B2+3,25) → The divisor is 25, but the alphabet has 26 letters, so the wrap-around happens at the wrong place.
Section C: Definitions
Q1
=MOD(number, divisor) — Returns the remainder after division, so numbers wrap around. =MOD(27,26) gives 1, which is how a Caesar shift loops from Z back to A.Section D: Fill in the Blanks
Q1
=MOD(27,26)Q2
=MOD(27,26)Q3
=MOD(-1,26)Q4
=MOD(A2+3,26)Q5
=MOD(A2-3,26)Section E: Spot the Error
Q1
D — The arguments are the wrong way round. MOD takes the number first and the divisor second. C2 is 28, so =MOD(C2,26) gives 2 and Z wraps round to C.
Q2
B — The spreadsheet function is called MOD, not MODULO. MODULO gives a #NAME? error. It should be =MOD(A1,26).
Q3
C — The divisor must be 26, the number of letters. A negative divisor gives a negative result (-3). =MOD(B1,26) turns -3 into 23, so A shifted back 3 wraps to X.
Q4
C — CODE turns a character into a number, and it needs text. To turn the number 69 into a letter you use CHAR. It should be =CHAR(B1+65).
Q5
A — The number and divisor are swapped. =MOD(26,27) gives 26, not 1. To wrap 27 around the alphabet you need =MOD(27,26).