• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

jeffreyweir

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"
 
Hi, jeffreyweir!

What about this?:

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

39 length in Spanish... but 32 in English :)

Regards!
 
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.
 
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
 
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?
 
SirJB7, that formula was a winner in my book. Regardless, stop your dancing and get down from the gold podium. ;-)
 
Hi, jeffreyweir!

With the new rules...

=ENTERO((FILA()-1)/X)*10+RESIDUO(FILA()-1;X)+1 -----> 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... :p
 
That formula is so beautiful and thornless that i'm going to do a tango with it between my lips. Congrats, dance away.
 
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!
 
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

:(
 
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.
 
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... :p
 
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?
 
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!
 
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.
 
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)
Adds 5 characters to your original formula.
 
You must log in or register to reply here.
Back
Top