# 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?

2. ## Re: Formula for AABCCBAABCCBAA sequence

Which year do you start from ?

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...

4. ## Re: Formula for AABCCBAABCCBAA sequence

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

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 ;-) )

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...

7. ## Re: Formula for AABCCBAABCCBAA sequence

But you said this:

based on the year,

8. ## Re: Formula for AABCCBAABCCBAA sequence

Formula:
`Please Login or Register  to view this content.`

9. ## Re: Formula for AABCCBAABCCBAA sequence

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

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

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.

12. ## Re: Formula for AABCCBAABCCBAA sequence

That's helping a lot. Thanks!

How should I link that to a year?
Attachment 709400

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...

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

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.

16. ## Re: Formula for AABCCBAABCCBAA sequence

Here's the requested workbook

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

18. ## Re: Formula for AABCCBAABCCBAA sequence

This is awesome!!! Thanks so much!

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

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

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