Excel formula

ashliiii

Board Regular
Joined
Feb 27, 2011
Messages
61
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
Joined
Sep 25, 2012
Messages
163
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
Joined
Jul 8, 2014
Messages
2,452
Office Version
365, 2010
Platform
Windows
Small typo in the above formula. Should be:

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

ashliiii

Board Regular
Joined
Feb 27, 2011
Messages
61
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.
 

Forum statistics

Threads
1,085,499
Messages
5,384,050
Members
401,876
Latest member
MHjortkjaer

Some videos you may like

This Week's Hot Topics

Top