Excel formula

ashliiii

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

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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)
 
Upvote 0
Small typo in the above formula. Should be:

=IF(COUNTIFS($B$1:B1,B1)/B1=1,B1+1,B1)
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top