How to Make Cells Automatically Translate Numbers and Letters?

Hi, I know the basics of excel but I am stuck with trying to create a table for working out something personal.

There are three things I am stuck with;

1) I want to be able to set up a function so that when I enter a number in the table, it will be translated into another number (which is relevant to my formula) at the bottom of the table. For example, if I put the number 1 in B3, it will be translated into 10 in B5. 2 would be translated into 9.8, 3 would be 9.6 etc.

2) This is something specific about square-rooting. I want the following to happen;

- If I enter 1 in D3, D5 will be 4.
- If I enter 2 in D3, D5 will be the square root of 4 (2).
- If I enter 3 in D3, D5 will be the square root of 4 to the power of 2 (or at least I think that is the right way to word it, basically when you square root 4 and then square root it again) which is around 1.41
- If I enter 4 in D3, D5 will be the quare root of 4 to the power of 3

etc. etc.

3) Is it possible to have letters converted into numbers and summed. For example, if A = 1 and B = 2, can I write BBABAAABAB in a cell and it will be summed in the cell below?

Please, if you can help on any of these, I will be very grateful.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Using the LOOKUP function would be beneficial here.

Set up a table of values, with first column in ascending order (this is the possible choices made in B3) and in next column relevant values to be returned into B5

Then use =LOOKUP(B3,X1:Y100) where X1:Y100 contains table.

You can next that function into other functions.. to get results for second question...

for third question, create another table of values, listing A, B, C, etc in one column, in ascending order, with their respective values in adjacent column, then formula:

=SUMPRODUCT(--(LOOKUP(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),F1:G10)))

where A1 contains your string to sum and F1:G10, the corresponding values to apply.