SOLVED

Who can explain this to me?

Copper Contributor

 

Who can explain this to me? 

I used the formula =UNIQUE(RANDBETWEEN(9788723527370120, 9788723527370990))Skærmbillede 2022-02-09 163400.pngDBETWEEN(9788723527370120, 9788723527370990))

10 Replies
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.
Do you have ant idea on how to solve this? i need a list with unique numbers within this range.
best response confirmed by Ohayonson (Copper Contributor)
Solution

@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?

@OhayonsonSee attached. You should apply Unique in column B not A.

@jitinm Thank you very much!

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.

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? 

 

@Hans Vogelaar 

@Ohayonson 

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.

@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. 

@Sergei Baklan 

Thanks, Sergei!

1 best response

Accepted Solutions
best response confirmed by Ohayonson (Copper Contributor)
Solution

@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?

View solution in original post