hello,
I am trying to make a set of random numbers, and sometimes getting error( i guess because the value is repeated)
my question is:
If can have this formula works with no repeat?
If I can excluding some numbers from the list (like random between 1,17 but No 10)
and if can have a number come up more often (Im not sure maybe something like: RANDBETWEEN(1,17),2
Thank you so much
screenshot.5892.jpg
Last edited by Excl3454; 03-20-2021 at 02:19 PM.
Hi Excl,
See the attached with how I do the "no repeats" problem. I use a normal Rand() and then rank the random numbers. This ranking will (should) never give the same number twice..
Rand with no repeats.xlsx
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
With 1-27 in F1 to F27.... and this in G1, copied down:
=RAND()+IF(COUNTIF($A$2:$C$2,F1)>0,NA(),0)
Then use this formula, in A2, copied across:
=CHOOSE(COLUMN(),RANDBETWEEN(1,6),RANDBETWEEN(7,17),RANDBETWEEN(18,27),INDEX($F$1:$F$27,MATCH(AGGREGATE(15,6,$G$1:$G$27/(NOT(ISNA($G$1:$G$27))),1),$G$1:$G$27,0)))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Thank you so much
Thank you very much. nice of you.Originally Posted by Glenn Kennedy
possible to exclude one number ( for example number 10) and can I just drag the code for about 10 rows?
thanks again
or this, for non-repeating RN's between 1 & 27:
=IFERROR(AGGREGATE(15,6,ROW($1:$27)/NOT(COUNTIF($A$1:A1, ROW($1:$27))), RANDBETWEEN(1,28-ROW(A1))),"")
You seem to be using an older version of Excel than me. So, please refer to the attached file. If the formulae are enclosed within a pair of { }, these are array formulae.
These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.
You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
Thank you again, I use 2019. it was very nice of you again
Yes... Modify the formula in G1, copied down, to exclude all numbers listed in column I (here, only 10 is excluded).
=RAND()+IF(COUNTIF($A$2:$C$2,F1)>0,NA(),0)+IF(COUNTIF(I:I,F1)>0,NA(),0)
Many thanks,,wish you a good weekend
You're welcome and thanks for the feedback. BtW... is the formula at Post 6 an array formula or not?? (I had hoped that it would NOT be... but the compatability checker suggested that it was).
yes, is array. I am using excel 2019 but even with Ctrl shift enter unable to drag the code down.
Possible to ask you the last worksheet you attached pull it down for about 20 or 30 rows please?
Ah yes, I missed that bit.... which does complicate things a little bit!!
You might need to consider the use of VBA.... as to get this to work with a formula you will need (I think) one helper column per row copied down.
but, even without VBA... it can be done.
The best
sorry , bother you lotsThe best! but Exclusions list i snot Excluding now,,no 10 is in the randoms now.
may you look at this attachment please...Many many thanks
Last edited by Excl3454; 03-21-2021 at 11:24 AM.
This is getting complicated. Is this some sort of lottery simulation? If so, maybe this code will work for you. Rick Rothstein, a regular contributor here wrote this some years ago. Since I know absolutely nothing about VBA I am simply repeating it here.
=PickExcept(6,49,10,11,12)
Please Login or Register to view this content.
Where the first number is the number of balls to be drawn, the second is the total number from which the draw can be made and ANYother comma separated numbers are those whichare EXCLUDED.
Many Many thanks,,,,you are the best and sorry again if i asked too muchThis is getting complicated. Is this some sort of lottery simulation? If so, maybe this code will work for you. Rick Rothstein, a regular contributor here wrote this some years ago. Since I know absolutely nothing about VBA I am simply repeating it here.
=PickExcept(6,49,10,11,12)Please Login or Register to view this content.
Where the first number is the number of balls to be drawn, the second is the total number from which the draw can be made and ANYother comma separated numbers are those whichare EXCLUDED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks