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

Incrementing numbers

Asheesh

Asheesh

Excel Ninja
How can i achieve this...

I want a formula to show the incrementing numbers like 1, 3, 6, 9...so on and so forth..in a row..
The formula should work anywhere on the sheet...I paste the data..and then drag down..it should pop up this sequential order..but without using VBA and offset..

I am currently using =IF(((ROWS(B3:$B$3)-1)*3)=0,1,((ROWS(B3:$B$3)-1)*3))
but this is hardcoded..it works fine if used in cell B3 and then dragged down..
 
Last edited:
Something like this?
=IF(COLUMNS($A$1:A1)=1,1,(COLUMNS($A$1:A1)-1)*3)
 
Last edited:
@GFC - Thanks mate...unfortunately, I am looking for some thing else...It is the same approach that I am already using(see the formula in post #1)... in both the cases it works from a specific cell...I am looking for something that is not hardcoded..
 
@Asheesh - It is not that hard coded...

you can copy the formula wherever you want in the sheet and drag it
left
=IF(COLUMNS($A$1:A1)=1,1,(COLUMNS($A$1:A1)-1)*3)
or down
=IF(ROWS($A$1:A1)=1,1,(ROWS($A$1:A1)-1)*3)

But it is important not to delete Row 1 or column 1, else you get #ref!
 
Agree...but I was looking for something on lines of INT or MOD or CEILING..instead of a row or column...not really sure if that is possible..
 
Hi Asheesh ,

I am not able to understand your argument about the formula you are using :

=IF(((ROWS(B3:$B$3)-1)*3)=0,1,((ROWS(B3:$B$3)-1)*3))

The segment ROWS(B3:$B$3) will evaluate to 1 where ever you enter it ; thereafter , if this formula is copied down , the formula will return values of 2 , 3 , 4 ,...

How do you say that this formula will work only if it is entered in cell B3 ?

Or do you mean that you want a formula which when it is copied from cell B3 or any other cell where it is entered , to a different cell , it should again start from 1 ?

If you can ensure that the cell above the cell where you enter this formula will be blank , then you can use something like this :

=IF(cellabovethiscell="",1,IF(cellabovethiscell=1,3,cellabovethiscell+3))

Suppose you have entered this formula in B3 ; it would have been :

=IF(B2="",1,IF(B2=1,3,B2+3))

Now , if you wish to copy this formula to cell Q23 , it will become :

=IF(Q23="",1,IF(Q23=1,3,Q23+3))

Now if you copy this formula down to cells Q24 , Q25 , Q26 ,... , the results will be 3 , 6 , 9 ,...

Narayan
 
Hi Narayan,

Thanks for your reply...please ignore the above formula..let me explain..

I was actually trying to help one of the members here with a formula..http://chandoo.org/forum/threads/text-number-in-different-column.19230/
I first gave him an array formula...and then I was thinking to go for a non-array solution...therefore arrived at this...INDEX($A$2:$A$13,COLUMNS($A$1:A$1)+(ROWS($A$1:$A1)-1)*$J$1,1)

My question - Is it possible to replace Columns & Rows function in the above formula with INT or MOD or Ceiling...?

Regards
Asheesh
 
You must log in or register to reply here.
Back
Top