+ Reply to Thread
Results 1 to 8 of 8

Using IF statement for Letter Sequences

  1. #1
    Registered User
    Join Date
    12-04-2014
    Location
    london
    MS-Off Ver
    ms office 10
    Posts
    3

    Unhappy Using IF statement for Letter Sequences

    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.

    Hope you guys can help..

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,375

    Re: Using IF statement for Letter Sequences

    I have no idea what you are trying to achieve. Can you explain?

  3. #3
    Registered User
    Join Date
    12-04-2014
    Location
    london
    MS-Off Ver
    ms office 10
    Posts
    3

    Re: Using IF statement for Letter Sequences

    LOL

    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.

    [I]Then counting back in the alphabet we have C,B,A,Z[/]

    The third letter output is Y.

    E.t.c.

    Hope the above makes sense..

  4. #4
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Using IF statement for Letter Sequences

    Is this what you're looking for?
    Attached Files Attached Files
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Using IF statement for Letter Sequences

    Give this a whirl

    LetterSeries.xlsx

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Using IF statement for Letter Sequences

    Maybe ...

    Row\Col
    A
    B
    C
    1
    0
    A
    B1: =CHAR(MOD(A1,26) + 65)
    2
    5
    F
    B2 and down: =CHAR(MOD(CODE(B1) - 65 + A2, 26) + 65)
    3
    5
    K
    4
    2
    M
    5
    9
    V
    6
    6
    B
    7
    8
    J
    8
    10
    T
    9
    7
    A
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Registered User
    Join Date
    12-04-2014
    Location
    london
    MS-Off Ver
    ms office 10
    Posts
    3

    Re: Using IF statement for Letter Sequences

    Wow.. Thanks guys for taking a look..

    Quote Originally Posted by Ace_XL View Post
    Is this what you're looking for?
    Thats the closest one that I got.

    It works for adding on the alplhabet..

    What I require is one for...

    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.

    [I]Then counting back in the alphabet we have C,B,A,Z[/]

    The third letter output is Y.

    E.t.c.

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Using IF statement for Letter Sequences

    Row\Col
    A
    B
    C
    1
    I
    B1: Input
    2
    5
    D
    B2 and down: =CHAR(MOD(CODE(B1) - 65 - A2, 26) + 65)
    3
    5
    Y
    4
    1
    X
    5
    1
    W

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] If statement for identifying a cell that starts with a letter or number
    By Stephen R in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-25-2014, 09:46 AM
  2. Change letter to number using if statement
    By pickledude in forum Excel General
    Replies: 14
    Last Post: 04-24-2012, 08:32 PM
  3. If Statement with data validation based on a letter
    By dagindi in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-24-2011, 11:23 AM
  4. Replies: 9
    Last Post: 11-16-2009, 07:16 PM
  5. Chart of letter sequences
    By Ipsl in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 01-28-2008, 06:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1