CODEWITHSUNDEEP
excelexcel-formula
Andrew Goulart
Andrew Goulart

Reputation: 66

Find Excel Formula For Number Pattern

I am trying to replicate the following pattern of numbers with an excel formula. I am really close, but not quite there, and I could hack it with an if / then statement, but that isn't clean and the formula isn't right.

Here is the pattern of numbers I'm trying to turn into a formula.

1 2 3 4 5 6 2 3 4 5 6 1 3 4 5 6 1 2 4 5 6 1 2 3 5 6 1 2 3 4 6 1 2 3 4 5

Here is the excel formula that I have that will do this very very closely

=MOD(MOD(ROW()-1,6)+1+FLOOR(((ROW()-1)/6),1),6)

This formula spits out the following set of numbers

1 2 3 4 5 0 2 3 4 5 0 1 3 4 5 0 1 2 4 5 0 1 2 3 5 0 1 2 3 4 0 1 2 3 4 5

Now I know I could do a simple =if(formula=0,6,formula) but that defeats the purpose of trying to get this to expand out with a single formula. I'm hoping someone where can help me see what it is I'm missing to make this work.

Thanks!

Andrew

Upvotes: 0

Views: 1803

Answers (3)

maybeWeCouldStealAVan
maybeWeCouldStealAVan

Reputation: 15610

I think you want:

=MOD(MOD(ROW()-1,6)+FLOOR(((ROW()-1)/6),1),6)+1

Just move the +1 out of the MOD.

Simpler, for my taste:

=MOD(ROW()+INT(ROW()/7)-1,6)+1

Upvotes: 0

MPękalski
MPękalski

Reputation: 7103

What about this one?

=MOD(MOD(MOD(MOD(MOD(MOD(MOD(ROW()-1,6)+1+FLOOR(((ROW()-1)/6),1),6)+1,6),5),4),3),2)*6+MOD(MOD(ROW()-1,6)+1+FLOOR(((ROW()-1)/6),1),6)

EDIT: the first part is taking care about the troublesome zero. It is your initial formula + 1, but put into 5 different MOD for 2, 3, 4, 5, 6, and then multiplied by 6. So each number different than 0 from your formula will become 0 and 0 will become 1.

Upvotes: 0

user1646196
user1646196

Reputation: 588

Building on your original formula here's a great big chunk of ugly for you:

=MOD(MOD(ROW()-1,6)+1+FLOOR(((ROW()-1)/6),1),6) +
6*FLOOR(ABS(COS(90*MOD(MOD(ROW()-1,6)+1+FLOOR(((ROW()-1)/6),1),6))),1)

Of course if you can use two columns you could compute

column A =MOD(MOD(ROW()-1,6)+1+FLOOR(((ROW()-1)/6),1),6)

Then in column B for each row n:
Bn = An+6*FLOOR(ABS(COS(An)),1)

Don't know about you but I feel kind of dirty after that...

Upvotes: 1

Related Questions