Using IF statement for Letter Sequences

macdadi112

New member
Joined
Dec 5, 2014
Messages
1
Reaction score
0
Points
0
:pout:

Hi

I cannot get my head round letter sequences (seriously I've tried)

What you have is the full alphabet and increasing in value I can do..

e.g. D ,G, J, M, P (You will start the alphabet again if you reach the end).

What I'm pulling my hair on is going backwards especially when you have to start all over again.

e.g I, D, Y, T, O

What I was thinking something along the line of

IF(65+C14+(3*D14)<=90,CHAR(65+C14+(3*D14)),CHAR(65+((C14+(3*D14))+65)-91))

The following are found in C14 and D14,..

rnd no seq no
20 5

The above are generated at random.

e.g. What I'm trying to do is output the letters of the alphabet..

They can start at any letter and then they increase/decrease by a random number(1-5).

Lets say the first letter output is I..

Then counting back in the alphabet we have H,G,F,E

The second letter output is D.

Then counting back in the alphabet we have C,B,A,Z

The third letter output is Y.

E.t.c.
 
I can see that formula gives a letter, but why does 1 and return E, and what should 1 and -1 return?
 
I can see that 0 and 0 return "A" and 25 and 0 return "Z". So I'm assuming that C14 generates numbers from 0 to 25 and D14 generates numbers from -5 to +5.
Your formula seems to work fine for returning letters from A to Z, although a slightly simplified version of your formula is:
=IF(C14+3*D14<26,CHAR(65+C14+3*D14),CHAR(39+C14+3*D14))
I still don't know what you're asking. I think you're saying that you can get the first letter but not sure how to get the next one?
If so, then if that formula above is in cell G3, then in Cell H3 enter this:
=CHAR(CODE(G3)+D14)
If you are dealing with negative numbers then you'll also need to allow for the chance of a number falling below char 65.
E.g. 2 in C14 and -5 in D14 would need to have 26 added to it and the same rule for cell H3
 
Here's an example of what I mean, although you'll probably need to somehow make sure that D14 doesn't ever fall on 0 as it would make all 5 letters the same.
 

Attachments

  • code.xlsx
    9.3 KB · Views: 5
Back
Top