LAB 5

Vigenere

K

E

Y

W

O

R

D

The upper left cell is B2.

encipherment

v

i

g

e

n

e

r

I entered the letters you see.

and

e

d

e

m

o

n

s

decipherment

t

r

a

t

i

o

n

In the upper left cell I entered

Numerical

22

9

7

5

14

5

18

=code(B3) - code("a") + 1

equivalents

5

4

5

13

15

14

19

Then I copied it and

of letters

20

18

1

20

9

15

14

pasted it into the other cells.

In the upper left cell I entered

KEYWORD

32

13

31

27

28

22

21

=B7 + code(B$2) - code("A")

shifted

15

8

29

35

29

31

22

Then I copied it and

values …

30

22

25

42

23

32

17

pasted it into the other cells.

In the upper left cell I entered

… reduced

6

13

5

1

2

22

21

=26 - mod(26 - B11, 26)

modulo 26

15

8

3

9

3

5

22

Then I copied it and

4

22

25

16

23

6

17

pasted it into the other cells.

In the upper left cell I entered

and converted

F

M

E

A

B

V

U

=char(B17 + code("A") - 1)

to capital

O

H

C

I

C

E

V

Then I copied it and

letters

D

V

Y

P

W

F

Q

pasted it into the other cells.

I could have done it all in one messy formula:

=char(26 - mod(26 - (code(B3)-code("a")+1+code(B$2)-code("A"), 26) + code("A") - 1)

F

M

E

A

B

V

U

O

H

C

I

C

E

V

D

V

Y

P

W

F

Q

DO THIS:

Exercise:  Figure out how to program the decipherment.

THEN WITH YOUR OWN KEYWORD ENCIPHER AND DECIPHER YOUR OWN SECRET MESSAGE.