Forum Discussion

Copper Contributor

Feb 09, 2022

Solved

Who can explain this to me?

Who can explain this to me? I used the formula =UNIQUE(RANDBETWEEN(9788723527370120, 9788723527370990)) DBETWEEN(9788723527370120, 9788723527370990))

Formulas and Functions

HansVogelaar
Feb 09, 2022
Ohayonson

Let'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?

Iron Contributor

Feb 09, 2022

This number should not have come twice if the range in the Unique formula had been between say A1 to A500. You are using Unique in each cell. That is why they can repeat in the list.

Copper Contributor

Feb 09, 2022

Do you have ant idea on how to solve this? i need a list with unique numbers within this range.

jitinm
Iron Contributor
Feb 09, 2022
OhayonsonSee attached. You should apply Unique in column B not A.
- Ohayonson
  Copper Contributor
  Feb 09, 2022
  jitinm Thank you very much!
HansVogelaar
MVP
Feb 09, 2022
Ohayonson

Let'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?
- Ohayonson
  Copper Contributor
  Feb 10, 2022
  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?
  
  HansVogelaar
  - SergeiBaklan
    Diamond Contributor
    Feb 10, 2022
    Ohayonson
    
    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.
- Ohayonson
  Copper Contributor
  Feb 09, 2022
  The reason why i need this long number is because i need a nubmer which is similar to an isbn nr. with the prefix 9788723527370 but i cam make it shorter so the prefix is 978872352737.
  Thank you very much for your time and answer.

Resources