# Formula Challenge 015 - Incrementing numbers with 1 to x as right-most digit

#### jeffreyweir

##### Active Member
Formula Challenge 015 - Incrementing numbers with 1 to 7 as right-most digit

Okay, here's an interesting one. Write a formula that you can input in cell A1 and drag down that returns the below pattern. My formula is 38 characters long.

This is relatively easy to do, but to do it in 38 characters might have you scratching your collective heads

1

2

3

4

5

6

7

11

12

13

14

15

16

17

21

22

23

24

25

26

27

...

EDITED

Check topic title: OP changed "7" by "x"

#### SirJB7

##### Excel Rōnin
Hi, jeffreyweir!

=DEC.A.OCT(FILA()+ENTERO((FILA()-1)/7)) -----&#62; in english: =DEC2OCT(ROW()+INT((ROW()-1)/7))

39 length in Spanish... but 32 in English

Regards!

#### jeffreyweir

##### Active Member
Freakin' genius. You've got me scratching my head!

#### jeffreyweir

##### Active Member
Okay, I'm going to have to amend this challenge slightly so that SirJB7 can't strut his Jagger strut in 8/8 time.

Instead of Incrementing numbers with 1 to 7 as right-most digit, write a formula that does 1 to x as right most digit, where x is an integer from 1 to 9. For instance, when x = 8 then the right most digit will be 1 to 8.

define x as named range, and put your value in there.

#### jeffreyweir

##### Active Member
Maybe a kindly moderator will take pity on me, and change the title of the thread to:

Formula Challenge 015 - Incrementing numbers with 1 to x as right-most digit

#### SirJB7

##### Excel Rōnin
Hi, jeffreyweir!

Topic changed as required.

What a pity that 30" solution formula isn't still a winner... you changed the rules after publishing, that's not fair... I feel as if a speck had entered in my mood... my spirit's light is slaking...

Regards!

PS: Shouldn't you add too that "x" should be in the range from 0 to 9?

#### jeffreyweir

##### Active Member
SirJB7, that formula was a winner in my book. Regardless, stop your dancing and get down from the gold podium. ;-)

#### SirJB7

##### Excel Rōnin
Hi, jeffreyweir!

With the new rules...

=ENTERO((FILA()-1)/X)*10+RESIDUO(FILA()-1;X)+1 -----&#62; in english: =INT((ROW()-1)/X)*10+MOD(ROW()-1,X)+1

Spanish 46 but English 37.

Regards!

PS: If you don't mind I'll still remain dancing in the platinum podium for a while, may I? 45" this time...

#### jeffreyweir

##### Active Member
That formula is so beautiful and thornless that i'm going to do a tango with it between my lips. Congrats, dance away.

#### jeffreyweir

##### Active Member
For some reason I'd forgotten that you could take 1 away from ROW().

So I was using this:

=(MOD(ROW(),-x)+x)+INT(ROW()/x-1/x)*10

...with my self-decided 'genius' feature being the use of -x in the MOD function, which I then added x to, in order to get around the fact that MOD(ROW(),x) alone would return 1,2,3,4,5,6,7,0 rather than the desired 1,2,3,4,5,6,7,8

But as your dancing has shown, MOD(ROW()-1,x)+1 is fancier footwork than I could ever hope to emulate.

Man, self-delusion sucks!

#### SirJB7

##### Excel Rōnin
Hi, jeffreyweir!

I always hate the workaround that it has to be done with MOD function when using it directly:

=MOD(ROW(),x)

which retrieves 1,2,3,4,5,6,0 instead of the most frequently desired 1,2,3,4,5,6,7 for which this should be written:

=MOD(ROW(),x)+(1-SGN(MOD(ROW(),x)))*x

Regards!

PS: Just to avoid IF using...

EDITED

PS2: Years writing the sign version formula and now I happen to find out this smarter one:

=MOD(ROW(),X)+(MOD(ROW(),X)=0)*X

#### jeffreyweir

##### Active Member
Okay, how about we put in a joint entry, and dance at the top together. You can even lead, so long as you don't step on my toes.

=INT((ROW()-1)/x)*10+MOD(ROW(),-x)+x

36 Characters in English.

#### SirJB7

##### Excel Rōnin
Hi, jeffreyweir!

You well deserve the gold podium, come on, climb up... but remember I'm taller than you.

Regards!

PS: 1 char is 1 char... tautologically speaking it'll always fit in Kant's thing and entity definitions... but not the zero, the absence of any thing or entity...

#### jeffreyweir

##### Active Member
SirJB7: Isn't the MOD(ROW()-1,X)+1 you used in this challenge smarter than both of these:

=MOD(ROW(),x)+(1-SGN(MOD(ROW(),x)))*x

=MOD(ROW(),X)+(MOD(ROW(),X)=0)*X

And how the heck did you come by the moniker SirJB7?

#### SirJB7

##### Excel Rōnin
Hi, jeffreyweir!

Yes, it's nicer, in my preference order:

=MOD(ROW()-1,X)+1 , which I suddenly saw while still reading this topic's 1st post

=MOD(ROW(),X)+(MOD(ROW(),X)=0)*X , which it happened to cross my mind while at another post

=MOD(ROW(),x)+(1-SGN(MOD(ROW(),x)))*x , the classic and traditional.

About the nick it's the same I've been using since I was a child, i.e. from 2007 until now. And it origin should be tracked to BBS's era, being a mix of something that comes bottled in green, yellow and red, and other personal preferences and a choice that had to be made in a moment... and I kept it since then.

Regards!

#### moxmith

##### New Member
=(ROUNDUP(ROW()/x,0)-1)*(10-x)+ROW()
I believe this works as well. It's also 36 characters.

#### jeffreyweir

##### Active Member
Sweet! Nice work, Moxmith. In fact, it's 35 characters if you omit the zero in the ROUND argument:
=(ROUNDUP(ROW()/x,)-1)*(10-x)+ROW()
How did you come up with that? I never would have thought along those lines.

#### sambu

##### New Member
=ROW()+INT((ROW()-1)/7)*3
this is whit 25 Characters

#### shrivallabha

##### Excel Ninja
Nice formula, Sambu. Only thing : it is specific[which was OK when the challenge began]. I'd suggest following tweak for making it generic.
Code:
``=ROW()+INT((ROW()-1)/7)*(10-7)``
``=ROW()+INT((ROW()-1)/7)*(10-7)``