Feb 09 2022 07:36 AM
Who can explain this to me?
I used the formula =UNIQUE(RANDBETWEEN(9788723527370120, 9788723527370990))DBETWEEN(9788723527370120, 9788723527370990))
Feb 09 2022 07:46 AM
Feb 09 2022 07:49 AM
Feb 09 2022 07:59 AM
SolutionLet's say you want 100 random numbers, but list only the unique values:
=UNIQUE(RANDARRAY(100,1,9788723527370120,9788723527370990,TRUE))
Warning: keep in mind that ALL numbers will end in 0 because your numbers have 16 digits but Excel only displays 15 significant digits. Why not generate random numbers between 120 and 990?
Feb 09 2022 08:23 AM
@OhayonsonSee attached. You should apply Unique in column B not A.
Feb 09 2022 08:50 AM
Feb 10 2022 01:48 AM
I get a spill error when i paste
=UNIQUE(RANDARRAY(100,1,9788723527370120,9788723527370990,TRUE))
in a clean sheet at a1 cell.
Am i doing something wrong?
Feb 10 2022 01:51 AM
No, it appears to be a bug.
Paste the formula into any other cell - it should work.
After that it should work in A1 too.
Feb 10 2022 05:31 AM
That's floating error which could appear could not #SPILL! - Volatile Size (microsoft.com) Maybe considered as bug, but that's how dynamic arrays are designed.
Feb 09 2022 07:59 AM
SolutionLet's say you want 100 random numbers, but list only the unique values:
=UNIQUE(RANDARRAY(100,1,9788723527370120,9788723527370990,TRUE))
Warning: keep in mind that ALL numbers will end in 0 because your numbers have 16 digits but Excel only displays 15 significant digits. Why not generate random numbers between 120 and 990?