How to generate 1 to 10 in Excel without repeating?
I have 10 cells in Excel that I wanted randomly number between 1 & 10.
Therefore each cell will feature a number from 1 to 10 inclusive.
Ideally looking NOT to use macro or any other cells.
- garbo7441Lv 77 years agoFavorite Answer
Here is one, not too elegant, method to do as you wish using a single formula. However, you will have to adjust your options to enable the use of circular references. This formula assumes the non-repeating numbers 1-10 will be returned in cells A1:A10.
For Excel 2003, and prior:
Go to Tools > Options and select the Calculation tab. Check the 'Iteration' checkbox. Click 'OK'.
For Excel 2007/2010:
Go to File > Options and select the Formulas menu item.
Place a check mark in 'Enable iterative calculation' checkbox. Click 'OK'.
In cell A1, enter the formula:
Fill the formula down through cell A10.
Note: This formula will not recalculate when the Calculate event occurs, i.e. pressing F9. To create a new list of numbers in A1:A10, simply select cell A1 and drag the formula down through A10 again. Like I said, not too elegant. However, it does avoid VBA and other cells.
- Greg GLv 77 years ago
Do you care if the numbers repeat? If not, you can simply use RANDBETWEEN:
copy that down from A1 to A10.
If you want each number to be unique, use this in A2:
=LARGE(ROW($1:$10) * NOT(COUNTIF($A$1:A1, ROW($1:$10))), RANDBETWEEN(1, 11-ROW(A1)))
Confirm with CTRL + SHIFT + ENTER, not just enter since this is an array formula.
Copy down to A11.
- sakLv 43 years ago
#a million became into humorous, in spite of the undeniable fact that it did no longer truly stick to the subject count. i presumed #10 confirmed extra lyricism and that i enjoyed "incomplete like a play without narration". there have been some truly good similes in that one. #10 gets my vote.
- fathermartin121Lv 67 years ago
If you want random, then you have to allow for repetition, otherwise, the last cell will be determined and not random.