Worksheet on Character Formulae in Excel - Caesar's by mbw16088

VIEWS: 18 PAGES: 3

									                      Worksheet on Character Formulae in Excel - Caesar's Cypher

In this worksheet we are going to duplicate a code used by Julius Caesar more than 2000 years ago. He took
plain Latin and moved each letter of the alphabet 2 steps along (so A became C, B became D, C became E etc.
Y became A, and Z became B). We can do this in Excel using character formulae and a lot of Fill Down!

   1. Firstly, power up Excel. Now that you have a blank sheet in front of you, put the message that you want
      to encode in cell A1, for example WE ATTACK THE GAULS AT DAWN. Stick to capital letters
      without any punctuation - although you can use spaces. The spreadsheet that we are going to create will
      be able to cope with spaces, but we won't unnecessarily complicate the issue by getting it to filter out
      punctuation or have to deal with lower case letters. Leave cell A2 blank - that's where the coded
      message will appear!
   2. The first thing we have to do is to split that message up
      into its individual letters. We do this by using the MID()
      function to extract substrings which are only 1 letter long.
      Put the number 1 in cell A4, 2 in A5, 3 in A6 etc. You may
      find it easier to put the first few numbers in and then use
      Fill Down to put the other numbers in. You should go
      down as far as about A30, or at least until you reach the
      same number as the number of letters in the message (so
      for a message of 27 letters, such as the one above, you
      should go down to cell A31, which will contain the
      number 27).
   3. In cell B4, put the following formula:
                                                     =MID(A$1,A4,1)
      What this means is "Extract the single character from the text in cell A1 at the position specified by cell
      A4". Cell A4 contains the number 1, so this will extract the single character at position number 1 (W in
      my example).
      Please note: There is a dollar sign in the middle of the reference to A1 as we are about to fill the formula
      down the column and we don't want that reference to change. If we didn't put the dollar sign in, then as
      we filled down the column, the reference to A1 would change to A2, then to A3 etc. which is not what
      we want. We want all the letters to be extracted from A1.

   4. Fill the formula in cell B4 down the column so that it
      reaches the same row as the numbers that you put in
      column A. If you examine the formulae, you will find
      that the reference to A4 has changed to A5, A6 etc. but
      the reference to A$1 has stayed the same (because of the
      dollar). This means that the formula will extract the
      second character from your message in cell B5, the third
      character in B6 etc. Spaces count as characters, so some
      of the cells will appear blank (even though they have
      spaces in them).

   5. Put the word Offset in cell A3 and the number 2 in cell B3. This will be the number that we are going to
      add to the code of each letter to shift it along the alphabet. The number 2 will turn A into C, B into D
      etc.
   6. In cell C4 put the following formula:
                                            =IF(B4=" ",32,CODE(B4)+B$3)
      This translates into English as "Look at the character in B4. If it is a space, put the number 32 in this
      cell. Otherwise, use the code number for that character with the specified offset added on".
            The "code" values referred to are the ANSI (or ASCII, if you prefer) code values for the letters.
              Letter "A" is represented in Excel (indeed in any computer program on your machine) as the
              number 65, "B" as 66 etc.
              We treat spaces as special. We don't want those translated into other symbols or letters. The
               reason we use the number 32 whenever we find a space is that that is the code value for space
               itself, i.e. we are replacing a space with another space.
              By now, you will have guessed that we are going to fill the formula down the column. The dollar
               sign prevents the reference to B$3 from changing.

7. Now fill this formula down the C column as far as
   the other formulae were filled. You should end up
   with something like this. If you have filled the
   columns down further than the length of the
   message (i.e. there are some "spare" slots at the
   end), then you will find that the formula produces
   an error in those spare slots. This is because the
   formula is trying to calculate the CODE value of a
   blank cell (which doesn't have one). Don't let this
   bother you. We can just ignore those cells at the
   end. You may want to encode a longer message in
   future, in which case they will be needed.

   8. It is possible that adding the offset to the codes of the letters may push some of them beyond Z in the
      alphabet, in which case they will be coded by punctuation symbols. We need to implement a "wrap-
      around" system where Y translates to A and Z translates to B.
      With this in mind, put the following formula in cell D4
                                             =IF(C4 > CODE("Z"),C4-26,C4)
      This means "If the value in C4 is beyond the code value for Z, then subtract 26 from it (which gives the
      wrap-around), otherwise, just use C4 as it stands." Then fill this formula down the column.
      If your message doesn't contain any Ys or Zs, then this column will simply duplicate the values in
      column C. Any letter Ys or Zs, on the other hand, will be coded by the numbers 65 (A) and 66 (B).

   9. Now all we need to do is turn the numbers into
      letters and string them all together to form the
      coded message. In cell E4 put the following
      formula:
            =CONCATENATE(E3,CHAR(D4))
      The contents of this cell will be the contents of
      cell E3 (which is blank) with the character
      whose code is in D4 added on the end. Since
      E3 is blank, this will just be the character
      whose code is in D4.

       Fill this formula down the column. You will
       find that each cell will hold the contents of the
       cell above it with the character whose code is
       in the D column tacked on the end. The coded
       message will be built up letter by letter:

   10. The last step is to transfer the coded message into cell A2 for completeness. Put in cell A2 the following
       formula:
                                                          =E30
       This formula applies to this particular message. The final message has been built up in cell E30. Of
       course, you will need to change this depending on the exact length of your message:

You can change the code simply by changing the value of the offset (in B3) to any whole number from 1 to 25.
If made the offset 0, then each letter would be the same in code and you would end up with the plain message
again.
A slight improvement

It's rather a nuisance having to change the cell reference in A2 every time you want to put a message of a
different length in code. It would be so much nicer if it could cope with them automatically.
      Change the formula in C4 into the following:
                                =IF(A4 > LEN(A$1), 0, IF(B4 = " ", 32, CODE(B4)+B$3))
         What you had before was just the last part of that. This new formula translates as "If this slot is beyond
         the end of the message (the LENgth of the message in A$1), then put a 0 in this slot, otherwise check
         the character as before (is it a space etc.)".
      Fill the formula in C4 down the column, overwriting the formulae that are in there from the previous
         version. You won't notice any change in the first few slots, but at the end of the message you will see
         slots filled with 0s.

       You notice that 0s appear at the end of the the D column as well. This is because it is only designed to
       stop the numbers going beyond the code value of Z. However, the E column still reports an error
       because it is trying to add character 0 (which doesn't exist) on to the end of the message.
      Now we change the formula in column E so that it only adds the character whose code is in the cell on
       its left if that character isn't zero. If it is zero, then it ignores that character and simply duplicates the
       message in the cell above. Change the formula in E4 into the following:

                                  =IF(D4 > 0,CONCATENATE(E3,CHAR(D4)),E3)

       This simply means, "If D4 contains a proper character value (bigger than 0), then add it to the message
       in the cell above (E3). Otherwise, just use the message in cell E3 unadulterated".

      Fill the formula in E4 down the column overwriting the ones that were in there. You will find that if you
       fill down below the length of the message, then it stops growing and simply stays at its final length. You
       can now fill all the columns A to E down to row 100 (or 200 for that matter) knowing that the E column
       of the last row will contain a copy of the final coded message instead of an error. 100 rows means that
       you can cope with messages of up to 100 characters. If you think you may need more, make it row 200,
       or 2000.

      Finally, change the formula in A2 to
                                                           =E100

       (or E200 or whatever you have chosen as your final row). This means that you can change the message
       in A1 as much as you like (providing you don't go above 200 characters or whatever the magic length
       is), and the coded message should appear in A2

Decoding Messages

Putting messages into code is all very well, providing you can decode them afterwards. This can be done very
easily by putting the coded message into A1 and altering the value in one single cell. What single change would
you make?

Here is a sample message for you to decode

PX ATOX UXTMXG MAX ZTNEL EXML MKR MAX KNLLBTGL GHP

and another:

V PNZR V FNJ NAQ V PBHYQAG ORYVRIR ZL RLRF

								
To top