# Excel formula

#### ashliiii

##### Board Regular
Can someone kindly help me figure out the formula for the following case: If the cells A1 to A100 contain values 1 to 100, what formula should be in cell B1 so that when copied and pasted to cells B2 to B100, it will produce the following sequence: B1 = 1, B2 = 2, B3 = 2, B4 = 3, B5 = 3, B6 = 3, B7 = 4, B8 = 4, B9 = 4, B10 = 4, B11 = 5, B12 = 5, B13 = 5, B14 = 5, B15 = 5 etc etc, thank you for your time.

#### petertenthije

##### Board Regular
I fail to see the relation between column A and column B, so I hope I understood your question right.
This formula will provide you with one cell with number 1; two cells with number 2; three cells with 3; four cells with 4... etc.

In B1, place 1.
In B2 and beyond, place this formula: =IF(COUNTIFS(B\$1:B1,B1)/B1=1;B1+1,B1)

#### igold

##### Well-known Member
Small typo in the above formula. Should be:

=IF(COUNTIFS(\$B\$1:B1,B1)/B1=1,B1+1,B1)

Last edited:

#### petertenthije

##### Board Regular
Small typo in the above formula. Should be:

=IF(COUNTIFS(\$B\$1:B1,B1)/B1=1,B1+1,B1)
My version of excel uses “;” instead of “,”. I thought I’d changed them all. :D

#### ashliiii

##### Board Regular
I fail to see the relation between column A and column B, so I hope I understood your question right.
This formula will provide you with one cell with number 1; two cells with number 2; three cells with 3; four cells with 4... etc.

In B1, place 1.
In B2 and beyond, place this formula: =IF(COUNTIFS(B\$1:B1,B1)/B1=1;B1+1,B1)