+ Reply to Thread
Results 1 to 19 of 19

Formula for AABCCBAABCCBAA sequence

  1. #1
    Registered User
    Join Date
    04-24-2015
    Location
    Leiden
    MS-Off Ver
    2013
    Posts
    13

    Formula for AABCCBAABCCBAA sequence

    Hello,

    I am trying to create a formula that, based on the year, will generate a weeknumber between 30 and 32. The pattern should be 30,30,31,32,32,31,30,30,31..etc

    Is there a formula for this?

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,738

    Re: Formula for AABCCBAABCCBAA sequence

    Which year do you start from ?

  3. #3
    Registered User
    Join Date
    04-24-2015
    Location
    Leiden
    MS-Off Ver
    2013
    Posts
    13

    Re: Formula for AABCCBAABCCBAA sequence

    The startyear shouldn't matter I think.
    But
    2019 32
    2020 31
    2021 30
    2022 30
    2023 31

    etc...

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: Formula for AABCCBAABCCBAA sequence

    Why wouldn't the start year matter? How should one know where in the sequence to start?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Registered User
    Join Date
    04-24-2015
    Location
    Leiden
    MS-Off Ver
    2013
    Posts
    13

    Re: Formula for AABCCBAABCCBAA sequence

    Because I think the heart of the problem lies in that B only comes once in between a double occurrence of A and C (AA B CC B AA). I have no clue how to create such a sequence (and neither does the Fill-function in excel ;-) )

  6. #6
    Registered User
    Join Date
    04-24-2015
    Location
    Leiden
    MS-Off Ver
    2013
    Posts
    13

    Re: Formula for AABCCBAABCCBAA sequence

    So let's assume A1 holds the year, B1 should display the weeknumber. If A1 changes, so does B1.

    Am I making sense? Sorry if this comes a bit messy...

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: Formula for AABCCBAABCCBAA sequence

    But you said this:

    based on the year,

  8. #8
    Registered User
    Join Date
    04-24-2015
    Location
    Leiden
    MS-Off Ver
    2013
    Posts
    13

    Re: Formula for AABCCBAABCCBAA sequence

    Yes I did, but I assume the best way to go about this is to do
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,738

    Re: Formula for AABCCBAABCCBAA sequence

    So the sequence would be
    AA B CC B AA B CC B AA B CC B, Etc. ?

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,785

    Re: Formula for AABCCBAABCCBAA sequence

    This formula is independent of the year:

    =IF(MOD(ROWS($1:1),3)=0,1,MOD(INT((ROWS($1:1)-1)/3),2)*2)+30

    it returns the sequence starting with 30, i.e. 30 30 31 32 32 31 30 30 etc.

    Hope this helps.

    Pete

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: Formula for AABCCBAABCCBAA sequence

    I don't see why the year is relevant at all, sorry. I'll leave this to someone who gets it.

  12. #12
    Registered User
    Join Date
    04-24-2015
    Location
    Leiden
    MS-Off Ver
    2013
    Posts
    13

    Re: Formula for AABCCBAABCCBAA sequence

    That's helping a lot. Thanks!

    How should I link that to a year?
    Attachment 709400

  13. #13
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,738

    Re: Formula for AABCCBAABCCBAA sequence

    Quote Originally Posted by dreojs16 View Post
    So let's assume A1 holds the year, B1 should display the weeknumber. If A1 changes, so does B1.

    Am I making sense? Sorry if this comes a bit messy...
    Why dont you upload your excel properly?

  14. #14
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,785

    Re: Formula for AABCCBAABCCBAA sequence

    The attachment is invalid. Please follow the directions in the yellow banner at the top of the screen.

    Pete

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: Formula for AABCCBAABCCBAA sequence

    Pete - it just shows a cell with a spinner selector with 2019 currently selected and 31 (from your formula) in the adjacent cell.

  16. #16
    Registered User
    Join Date
    04-24-2015
    Location
    Leiden
    MS-Off Ver
    2013
    Posts
    13

    Re: Formula for AABCCBAABCCBAA sequence

    Here's the requested workbook
    Attached Files Attached Files

  17. #17
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,785

    Re: Formula for AABCCBAABCCBAA sequence

    Which year starts the sequence?

    There are 6 possible outcomes in your sequence, so if you take your year value (in A1) and subtract a fixed year which starts the sequence, and then take the remainder of that after dividing by 6, you will have a value that should be added on to the ROWS($1:1) term (twice) in the formula that I gave you, i.e. in B1:

    =IF(MOD(ROWS($1:1)+MOD(A1-start_year,6),3)=0,1,MOD(INT((ROWS($1:1)+MOD(A1-start_year,6)-1)/3),2)*2)+30

    Hope this helps.

    Pete

  18. #18
    Registered User
    Join Date
    04-24-2015
    Location
    Leiden
    MS-Off Ver
    2013
    Posts
    13

    Re: Formula for AABCCBAABCCBAA sequence

    This is awesome!!! Thanks so much!

  19. #19
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,785

    Re: Formula for AABCCBAABCCBAA sequence

    Actually, you don't need the ROWS($1:1) terms - I thought you would be copying the formula down a column. You can use this instead in B1:

    =IF(MOD(MOD(A1-2016,6)+1,3)=0,1,MOD(INT((MOD(A1-2016,6))/3),2)*2)+30

    This starts the sequence at the first 30 in 2016, so adjust this if required.

    Hope this helps.

    Pete

    P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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] Separate sequence of numbers with sequence of letters
    By dell001 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 07-02-2020, 07:09 AM
  2. Find a short-sequence in a longer sequence (Clash Royale Chests Challenge)
    By GeneralDisarray in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-08-2016, 12:20 PM
  3. Replies: 7
    Last Post: 06-08-2015, 08:39 AM
  4. HELP: How to - FORMULA SEQUENCE
    By Irlwizard in forum Excel General
    Replies: 2
    Last Post: 12-02-2013, 06:28 AM
  5. Replies: 0
    Last Post: 12-12-2012, 06:39 PM
  6. [SOLVED] Sequence Formula
    By helpbitte in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-02-2012, 12:01 PM
  7. Formula sequence
    By robertoc in forum Excel General
    Replies: 2
    Last Post: 10-21-2011, 12:44 PM

Tags for this Thread

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