# Formula for AABCCBAABCCBAA sequence

1. ## 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?  Register To Reply

2. ## Re: Formula for AABCCBAABCCBAA sequence

Which year do you start from ?  Register To Reply

3. ## Re: Formula for AABCCBAABCCBAA sequence

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

etc...  Register To Reply

4. ## Re: Formula for AABCCBAABCCBAA sequence

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

5. ## 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 ;-) )  Register To Reply

6. ## 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...  Register To Reply

7. ## Re: Formula for AABCCBAABCCBAA sequence

But you said this:

based on the year,  Register To Reply

8. ## Re: Formula for AABCCBAABCCBAA sequence

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

9. ## Re: Formula for AABCCBAABCCBAA sequence

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

10. ## 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  Register To Reply

11. ## 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.  Register To Reply

12. ## Re: Formula for AABCCBAABCCBAA sequence

That's helping a lot. Thanks!

How should I link that to a year?
Attachment 709400  Register To Reply

13. ## Re: Formula for AABCCBAABCCBAA sequence Originally Posted by dreojs16 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?  Register To Reply

14. ## Re: Formula for AABCCBAABCCBAA sequence

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

Pete  Register To Reply

15. ## 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.  Register To Reply

16. ## Re: Formula for AABCCBAABCCBAA sequence

Here's the requested workbook  Register To Reply

17. ## 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  Register To Reply

18. ## Re: Formula for AABCCBAABCCBAA sequence

This is awesome!!! Thanks so much!  Register To Reply

19. ## 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  Register To Reply

##### Users Browsing this Thread

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

#### Tags for this Thread #### 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