![]() ![]() Two things might happen though: 1.) You see a list of random unique values that is less than you specified 2.) You see a spill error (to the right in Figure A). For instance, the following function, shown in Figure A, will return a single column of five (seemingly) unique values between 1 and 20: You can’t force RANDARRAY() to return only unique values, but you can combine it with UNIQUE() to get the desired results. It is easy, but it isn’t a reasonable way to work unless what you’re doing is a one-time pass. Convert the values in the spill range to static values (also replacing the function) and then remove any duplicates using the Remove Duplicates tool on the Data tab. If you have the time, there’s a simple way to achieve random values with no duplicates. RANDARRAY() alone has the potential to return duplicate values, as do all the random functions. How to generate random values without duplicates, sort of To generate a random array of non-duplicate values, we’ll use three dynamic array functions: RANDARRAY(), UNIQUE(), and SEQUENCE().Currently, if the data source is in another workbook, both workbooks must be open for RANDARRAY() to function properly.RANDARRAY() will update results and resize when the source data changes if the source data is in a Table object. ![]() To learn more about Excel’s spill range, you can read How to use the spill range in Excel If there aren’t adequate cells in this range, RANDARRAY() returns a spill error.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |