Rookiebatman
New Member
- Joined
- Apr 18, 2013
- Messages
- 4
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?"
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?"
