RANDBETWEEN (Excel 2007)

[steveh]

Good morning

I am trying to get into using 2007 and I am experimenting with the RANDBETWEEN function and to do that I have made a worksheet to select Euro Lottery numbers. The basic layout is therefore 5 rows numbered ball 1, ball 2 etc. each with the formula =RANDBETWEEN(1,50) and 2 for the bonus balls that have the formula RANDBETWEEN(1,9).

What I had not allowed for was duplicates, is there something I can add to the formula to stop duplicates without using VBA. I have tried the RAND feature but that does not seem to allow me to put anything in between the brackets. Tushar Metha's site gives some code examples and some other examples using RAND and RANK but it was the RANDBETWEEN that I was particularly interested in

[HansV]

Neither RAND nor RANDBETWEEN have built-in support for generating unique random numbers; you need either VBA code or elaborate formulas involving other functions such as SMALL or RANK in combination with RAND.
RANDBETWEEN does not offer any specific advantage in this respect, so you might as well use one of the many existing solutions.

[steveh]

Neither RAND nor RANDBETWEEN have built-in support for generating unique random numbers; you need either VBA code or elaborate formulas involving other functions such as SMALL or RANK in combination with RAND.
RANDBETWEEN does not offer any specific advantage in this respect, so you might as well use one of the many existing solutions.

Hi Hans

Thanks for that advice. Not having seen this function in previous versions I thought it might offer more in flexibility so I was just experimenting and wondered if it could be more useful than just picking one number