Excel formula

[ashliiii]

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]

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]

Small typo in the above formula. Should be:

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

 

[petertenthije]

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]

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)

Thank you for your time, your formula works.