# Simple Way to Generate Dynamic Number Sequence

#### Rookiebatman

##### New Member
Hello, this is my first post, and I'm very much an Excel novice, so I apologize if I use the wrong terminology for things.

Being someone who generally only uses Excel for the most basic functions, I was pretty pleased with myself when I figured out how to use Excel to generate random numbers. Now, what I'm doing is using the random numbers to simulate drawing a card from a deck, and then removing the card from further selections. So, if I have 30 cards, I would generate a random number between 1 and 30, and then the next time, a number between 1 and 29, etc., until all cards have been selected, and then I would start over.

So I have a spreadsheet with images of all the cards in the deck, and what I want to do is have the number of the card in the cell below the card images, so that when I generate the random number, I can just see at a glance which card that number corresponds to. But what would be nice is if I could use conditional formulas to make it so that each of those cells has the next number in the sequence, unless I change the value to 0 (indicating that the card in the cell above it has been removed from the deck), at which point the value in the next cell will follow the sequence of the previous cell that is not zero. So, if the first four cells are numbered 1,2,3,4, and I change the value of the second cell to 0, then the values would automatically change to 1,0,2,3. And, of course, if I have two (or more) consecutive 0 values, then the cells after that would still pick up the sequence of the last non-zero value (so, if I changed the third cell to zero as well, then the previous example would become 1,0,0,2).

I think I know how to do that; right now the formula I'm using is "=IF(A2>0,SUM(A2+1),1)" for the second cell, and then "=IF(B2>0,SUM(B2+1),IF(A2>0,SUM(A2+1),1))" for the third cell, etc. I expect that this should work, my only problem is just the tedium of having to add the additional nested condition in each subsequent cell. I'm doing a sequence of sixty cells, so by the end of it, the formula will be pretty long. Is there any simpler or more concise way to make a conditional statement that essentially says "Pick up the sequence from the nearest whole number to the left?"

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Replies
3
Views
463
Replies
3
Views
117
Replies
3
Views
81
Replies
3
Views
346
Replies
6
Views
563

1,203,236
Messages
6,054,301
Members
444,715
Latest member
GlitchHawk

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

### Which adblocker are you using?

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

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