cyphering-decypering

  • Thread starter Thread starter BOB MCCLURE
  • Start date Start date
B

BOB MCCLURE

There is an elizabethan cypher that assigns 24 numbers to the 26
letters beginning with a=1, b=2, c=3, and so on except i&j both=9, and
u & v both=20.

I believe excel is used to discover number patterns in the sonnets of
Shakespeare and other Elizbethan poets. It seems to me each letter
being assigned a cell, the numeric equivalent could be ascertained and
thus words and lines or entire works summed.

However, the formula
"=if(a1=a,1,if(a1=b,2,if(a1=c,3.........if(a1=z,24,0)))))))))))))))))))))))))),
doesn't work.

I am looking for the correct formula to then copy to the spreadsheet.

Thanks,
Bob
 
Try VLOOKUP

First, set-up the reference table
in Sheet2, in A1:B26
--------------------------

Put in A1: =CHAR(ROW()+96)
Copy A1 down to A26
[this will fill the letters a,b,c,d... z in A1:A26]

List the numbers corresponding to the letters in B1:B26
(according to your specs)


In Sheet1
----------
Assuming the letters to be translated are in col A, A1 down

Put in B1:

=IF(ISNA(VLOOKUP(TRIM(A1),Sheet2!$A$1:$B$26,2,0)),"",VLOOKUP(TRIM(A1),Sheet2
!$A$1:$B$26,2,0))

Copy B1 down

Col B will return the numbers corresponding to the letters in col A
 
Bob, you're welcome..

Perhaps you would like to try the complete set-up below
which will allow you to get all the cipher totals?

Assuming your reference table below is listed
in Sheet2, in A1:E27
-------------------------
LETTER SIMPLE REVERSE SHORT KAYE
A 1 24 1 27
B 2 23 2 28
C 3 22 3 29
D 4 21 4 30
E 5 20 5 31
F 6 19 6 32
G 7 18 7 33
H 8 17 8 34
I 9 16 9 35
J 9 16 9 35
K 10 15 1 10
L 11 14 2 11
M 12 13 3 12
N 13 12 4 13
O 14 11 5 14
P 15 10 6 15
Q 16 9 7 16
R 17 8 8 17
S 18 7 9 18
T 19 6 1 19
U 20 5 2 20
V 20 5 2 20
W 21 4 3 21
X 22 3 4 22
Y 23 2 5 23
Z 24 1 6 24
Click to expand...

In Sheet1
-----------

List the labels: SIMPLE, REVERSE, SHORT, KAYE
across in B1:E1

Put in B2: =SUM(B3:B200), copy B2 across to E2

List the letters/names to be translated in A3 downwards

For example, list the letters for "WILL TUDOR" down in A3:A12

Put in B3:

=IF(ISNA(VLOOKUP(TRIM($A3),Sheet2!$A$2:$E$27,COLUMN(),0)),"",VLOOKUP(TRIM($A
3),Sheet2!$A$2:$E$27,COLUMN(),0))

Copy B3 across to E3, then copy down to row200

Note: Row200 is arbitrary and is to match the SUM formula placed in B2:E2.
Just adapt to suit if insufficient.

----
The cipher totals for SIMPLE, REVERSE, SHORT, KAYE will be given in B2:E2

For the example of "Will Tudor" in A3:A12 above,
the cipher totals for SIMPLE, REVERSE, SHORT, KAYE
will be calculated as: 126, 99, 36, 178

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
BOB MCCLURE said:
Shakespeare's Alphabet Cipher Keys (four ciphers, simple, reverse,
short, & kaye)
LETTER SIMPLE REVERSE SHORT KAYE
A 1 24 1 27
B 2 23 2 28
C 3 22 3 29
D 4 21 4 30
E 5 20 5 31
F 6 19 6 32
G 7 18 7 33
H 8 17 8 34
I 9 16 9 35
J 9 16 9 35
K 10 15 1 10
L 11 14 2 11
M 12 13 3 12
N 13 12 4 13
O 14 11 5 14
P 15 10 6 15
Q 16 9 7 16
R 17 8 8 17
S 18 7 9 18
T 19 6 1 19
U 20 5 2 20
V 20 5 2 20
W 21 4 3 21
X 22 3 4 22
Y 23 2 5 23
Z 24 1 6 24

The Sheet 1 reference table looks like this:

SIMPLE
A 1
B 2
C 3
D 4
E 5
F 6
G 7
H 8
I 9
J 9
K 10
L 11
M 12
N 13
O 14
P 15
Q 16
R 17
S 18
T 19
U 20
V 20
W 21
X 22
Y 23
Z 24

Thanks, it's magic! I guess the poems are entered directly onto sheet
1.

Excel gave me the error message for the two quotations ("")in Sheet 1
cell b2. I only have beginning excel, but I understand spreadsheets
are used in literature to demonstrate numerical patterns in
Shakespeare's Sonnets.

God Bless you,
Bob McClure

"Max" <[email protected]> wrote in message
Try VLOOKUP

First, set-up the reference table
in Sheet2, in A1:B26
--------------------------

Put in A1: =CHAR(ROW()+96)
Copy A1 down to A26
[this will fill the letters a,b,c,d... z in A1:A26]

List the numbers corresponding to the letters in B1:B26
(according to your specs)


In Sheet1
----------
Assuming the letters to be translated are in col A, A1 down

Put in B1:

=IF(ISNA(VLOOKUP(TRIM(A1),Sheet2!$A$1:$B$26,2,0)),"",VLOOKUP(TRIM(A1),Sheet2
!$A$1:$B$26,2,0))

Copy B1 down

Col B will return the numbers corresponding to the letters in col A

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----
BOB MCCLURE said:
There is an elizabethan cypher that assigns 24 numbers to the 26
letters beginning with a=1, b=2, c=3, and so on except i&j both=9, and
u & v both=20.

I believe excel is used to discover number patterns in the sonnets of
Shakespeare and other Elizbethan poets. It seems to me each letter
being assigned a cell, the numeric equivalent could be ascertained and
thus words and lines or entire works summed.

However, the formula
Click to expand...
"=if(a1=a,1,if(a1=b,2,if(a1=c,3.........if(a1=z,24,0))))))))))))))))))))))))
)),
doesn't work.

I am looking for the correct formula to then copy to the spreadsheet.

Thanks,
Bob
Click to expand...
Click to expand...
Click to expand...
 
Shakespeare's Alphabet Cipher Keys (four ciphers, simple, reverse,
short, & kaye)
LETTER SIMPLE REVERSE SHORT KAYE
A 1 24 1 27
B 2 23 2 28
C 3 22 3 29
D 4 21 4 30
E 5 20 5 31
F 6 19 6 32
G 7 18 7 33
H 8 17 8 34
I 9 16 9 35
J 9 16 9 35
K 10 15 1 10
L 11 14 2 11
M 12 13 3 12
N 13 12 4 13
O 14 11 5 14
P 15 10 6 15
Q 16 9 7 16
R 17 8 8 17
S 18 7 9 18
T 19 6 1 19
U 20 5 2 20
V 20 5 2 20
W 21 4 3 21
X 22 3 4 22
Y 23 2 5 23
Z 24 1 6 24

The Sheet 1 reference table looks like this:

SIMPLE
A 1
B 2
C 3
D 4
E 5
F 6
G 7
H 8
I 9
J 9
K 10
L 11
M 12
N 13
O 14
P 15
Q 16
R 17
S 18
T 19
U 20
V 20
W 21
X 22
Y 23
Z 24

Thanks, it's magic! I guess the poems are entered directly onto sheet
1.

Excel gave me the error message for the two quotations ("")in Sheet 1
cell b2. I only have beginning excel, but I understand spreadsheets
are used in literature to demonstrate numerical patterns in
Shakespeare's Sonnets.

God Bless you,
Bob McClure

Max said:
Try VLOOKUP

First, set-up the reference table
in Sheet2, in A1:B26
--------------------------

Put in A1: =CHAR(ROW()+96)
Copy A1 down to A26
[this will fill the letters a,b,c,d... z in A1:A26]

List the numbers corresponding to the letters in B1:B26
(according to your specs)


In Sheet1
----------
Assuming the letters to be translated are in col A, A1 down

Put in B1:

=IF(ISNA(VLOOKUP(TRIM(A1),Sheet2!$A$1:$B$26,2,0)),"",VLOOKUP(TRIM(A1),Sheet2
!$A$1:$B$26,2,0))

Copy B1 down

Col B will return the numbers corresponding to the letters in col A

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----
BOB MCCLURE said:
There is an elizabethan cypher that assigns 24 numbers to the 26
letters beginning with a=1, b=2, c=3, and so on except i&j both=9, and
u & v both=20.

I believe excel is used to discover number patterns in the sonnets of
Shakespeare and other Elizbethan poets. It seems to me each letter
being assigned a cell, the numeric equivalent could be ascertained and
thus words and lines or entire works summed.

However, the formula
"=if(a1=a,1,if(a1=b,2,if(a1=c,3.........if(a1=z,24,0))))))))))))))))))))))))
)),
doesn't work.

I am looking for the correct formula to then copy to the spreadsheet.

Thanks,
Bob
Click to expand...
Click to expand...
 
Bob, you're welcome..

Perhaps you would like to try the complete set-up below
which will allow you to get all the cipher totals?

Assuming your reference table below is listed
in Sheet2, in A1:E27
-------------------------
LETTER SIMPLE REVERSE SHORT KAYE
A 1 24 1 27
B 2 23 2 28
C 3 22 3 29
D 4 21 4 30
E 5 20 5 31
F 6 19 6 32
G 7 18 7 33
H 8 17 8 34
I 9 16 9 35
J 9 16 9 35
K 10 15 1 10
L 11 14 2 11
M 12 13 3 12
N 13 12 4 13
O 14 11 5 14
P 15 10 6 15
Q 16 9 7 16
R 17 8 8 17
S 18 7 9 18
T 19 6 1 19
U 20 5 2 20
V 20 5 2 20
W 21 4 3 21
X 22 3 4 22
Y 23 2 5 23
Z 24 1 6 24
Click to expand...

In Sheet1
-----------

List the labels: SIMPLE, REVERSE, SHORT, KAYE
across in B1:E1

Put in B2: =SUM(B3:B200), copy B2 across to E2

List the letters/names to be translated in A3 downwards

For example, list the letters for "WILL TUDOR" down in A3:A12

Put in B3:

=IF(ISNA(VLOOKUP(TRIM($A3),Sheet2!$A$2:$E$27,COLUMN(),0)),"",VLOOKUP(TRIM($A
3),Sheet2!$A$2:$E$27,COLUMN(),0))

Copy B3 across to E3, then copy down to row200

Note: Row200 is arbitrary and is to match the SUM formula placed in B2:E2.
Just adapt to suit if insufficient.

----
The cipher totals for SIMPLE, REVERSE, SHORT, KAYE will be given in B2:E2

For the example of "Will Tudor" in A3:A12 above,
the cipher totals for SIMPLE, REVERSE, SHORT, KAYE
will be calculated as: 126, 99, 36, 178

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
BOB MCCLURE said:
Shakespeare's Alphabet Cipher Keys (four ciphers, simple, reverse,
short, & kaye)
LETTER SIMPLE REVERSE SHORT KAYE
A 1 24 1 27
B 2 23 2 28
C 3 22 3 29
D 4 21 4 30
E 5 20 5 31
F 6 19 6 32
G 7 18 7 33
H 8 17 8 34
I 9 16 9 35
J 9 16 9 35
K 10 15 1 10
L 11 14 2 11
M 12 13 3 12
N 13 12 4 13
O 14 11 5 14
P 15 10 6 15
Q 16 9 7 16
R 17 8 8 17
S 18 7 9 18
T 19 6 1 19
U 20 5 2 20
V 20 5 2 20
W 21 4 3 21
X 22 3 4 22
Y 23 2 5 23
Z 24 1 6 24

The Sheet 1 reference table looks like this:

SIMPLE
A 1
B 2
C 3
D 4
E 5
F 6
G 7
H 8
I 9
J 9
K 10
L 11
M 12
N 13
O 14
P 15
Q 16
R 17
S 18
T 19
U 20
V 20
W 21
X 22
Y 23
Z 24

Thanks, it's magic! I guess the poems are entered directly onto sheet
1.

Excel gave me the error message for the two quotations ("")in Sheet 1
cell b2. I only have beginning excel, but I understand spreadsheets
are used in literature to demonstrate numerical patterns in
Shakespeare's Sonnets.

God Bless you,
Bob McClure

"Max" <[email protected]> wrote in message
Try VLOOKUP

First, set-up the reference table
in Sheet2, in A1:B26
--------------------------

Put in A1: =CHAR(ROW()+96)
Copy A1 down to A26
[this will fill the letters a,b,c,d... z in A1:A26]

List the numbers corresponding to the letters in B1:B26
(according to your specs)


In Sheet1
----------
Assuming the letters to be translated are in col A, A1 down

Put in B1:

=IF(ISNA(VLOOKUP(TRIM(A1),Sheet2!$A$1:$B$26,2,0)),"",VLOOKUP(TRIM(A1),Sheet2
!$A$1:$B$26,2,0))

Copy B1 down

Col B will return the numbers corresponding to the letters in col A

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----
BOB MCCLURE said:
There is an elizabethan cypher that assigns 24 numbers to the 26
letters beginning with a=1, b=2, c=3, and so on except i&j both=9, and
u & v both=20.

I believe excel is used to discover number patterns in the sonnets of
Shakespeare and other Elizbethan poets. It seems to me each letter
being assigned a cell, the numeric equivalent could be ascertained and
thus words and lines or entire works summed.

However, the formula
Click to expand...
"=if(a1=a,1,if(a1=b,2,if(a1=c,3.........if(a1=z,24,0))))))))))))))))))))))))
)),
doesn't work.

I am looking for the correct formula to then copy to the spreadsheet.

Thanks,
Bob
Click to expand...
Click to expand...
Click to expand...
 
Dear Max,

That's it! I have just created my first book on Excel. I don't know
how to thank you, I only thought to ask and you took the time to
answer. There really isn't anyone that dedicated, to anything I know
of! Excepting the Almighty of course. Max, if there's someway I can
repay you, please let me know.

God Bless,
Bob McClure

Max said:
Bob, you're welcome..

Perhaps you would like to try the complete set-up below
which will allow you to get all the cipher totals?

Assuming your reference table below is listed
in Sheet2, in A1:E27
-------------------------
LETTER SIMPLE REVERSE SHORT KAYE
A 1 24 1 27
B 2 23 2 28
C 3 22 3 29
D 4 21 4 30
E 5 20 5 31
F 6 19 6 32
G 7 18 7 33
H 8 17 8 34
I 9 16 9 35
J 9 16 9 35
K 10 15 1 10
L 11 14 2 11
M 12 13 3 12
N 13 12 4 13
O 14 11 5 14
P 15 10 6 15
Q 16 9 7 16
R 17 8 8 17
S 18 7 9 18
T 19 6 1 19
U 20 5 2 20
V 20 5 2 20
W 21 4 3 21
X 22 3 4 22
Y 23 2 5 23
Z 24 1 6 24
Click to expand...

In Sheet1
-----------

List the labels: SIMPLE, REVERSE, SHORT, KAYE
across in B1:E1

Put in B2: =SUM(B3:B200), copy B2 across to E2

List the letters/names to be translated in A3 downwards

For example, list the letters for "WILL TUDOR" down in A3:A12

Put in B3:

=IF(ISNA(VLOOKUP(TRIM($A3),Sheet2!$A$2:$E$27,COLUMN(),0)),"",VLOOKUP(TRIM($A
3),Sheet2!$A$2:$E$27,COLUMN(),0))

Copy B3 across to E3, then copy down to row200

Note: Row200 is arbitrary and is to match the SUM formula placed in B2:E2.
Just adapt to suit if insufficient.

----
The cipher totals for SIMPLE, REVERSE, SHORT, KAYE will be given in B2:E2

For the example of "Will Tudor" in A3:A12 above,
the cipher totals for SIMPLE, REVERSE, SHORT, KAYE
will be calculated as: 126, 99, 36, 178

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
BOB MCCLURE said:
Shakespeare's Alphabet Cipher Keys (four ciphers, simple, reverse,
short, & kaye)
LETTER SIMPLE REVERSE SHORT KAYE
A 1 24 1 27
B 2 23 2 28
C 3 22 3 29
D 4 21 4 30
E 5 20 5 31
F 6 19 6 32
G 7 18 7 33
H 8 17 8 34
I 9 16 9 35
J 9 16 9 35
K 10 15 1 10
L 11 14 2 11
M 12 13 3 12
N 13 12 4 13
O 14 11 5 14
P 15 10 6 15
Q 16 9 7 16
R 17 8 8 17
S 18 7 9 18
T 19 6 1 19
U 20 5 2 20
V 20 5 2 20
W 21 4 3 21
X 22 3 4 22
Y 23 2 5 23
Z 24 1 6 24

The Sheet 1 reference table looks like this:

SIMPLE
A 1
B 2
C 3
D 4
E 5
F 6
G 7
H 8
I 9
J 9
K 10
L 11
M 12
N 13
O 14
P 15
Q 16
R 17
S 18
T 19
U 20
V 20
W 21
X 22
Y 23
Z 24

Thanks, it's magic! I guess the poems are entered directly onto sheet
1.

Excel gave me the error message for the two quotations ("")in Sheet 1
cell b2. I only have beginning excel, but I understand spreadsheets
are used in literature to demonstrate numerical patterns in
Shakespeare's Sonnets.

God Bless you,
Bob McClure

"Max" <[email protected]> wrote in message
Try VLOOKUP

First, set-up the reference table
in Sheet2, in A1:B26
--------------------------

Put in A1: =CHAR(ROW()+96)
Copy A1 down to A26
[this will fill the letters a,b,c,d... z in A1:A26]

List the numbers corresponding to the letters in B1:B26
(according to your specs)


In Sheet1
----------
Assuming the letters to be translated are in col A, A1 down

Put in B1:

=IF(ISNA(VLOOKUP(TRIM(A1),Sheet2!$A$1:$B$26,2,0)),"",VLOOKUP(TRIM(A1),Sheet2
!$A$1:$B$26,2,0))

Copy B1 down

Col B will return the numbers corresponding to the letters in col A

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----
There is an elizabethan cypher that assigns 24 numbers to the 26
letters beginning with a=1, b=2, c=3, and so on except i&j both=9, and
u & v both=20.

I believe excel is used to discover number patterns in the sonnets of
Shakespeare and other Elizbethan poets. It seems to me each letter
being assigned a cell, the numeric equivalent could be ascertained and
thus words and lines or entire works summed.

However, the formula

"=if(a1=a,1,if(a1=b,2,if(a1=c,3.........if(a1=z,24,0))))))))))))))))))))))))
)),
doesn't work.

I am looking for the correct formula to then copy to the spreadsheet.

Thanks,
Bob
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

dates converted 1
Scrabble Value calculation for Welsh words 0
Cypher 3
IF formulas 2
Compound Formula for Substitution 3
Concantenate which keeps bold of some parts. 5
Excel Need Countifs Formula Help 0
Wrong data type 2

Back
Top