VBA and Formula for Lottery Random Number Selection

[Tina]

Require VBA and Formula to perform the following:

1. Choose two Random numbers between 1-34

2. The lowest number of the two must be subtracted from
the highest number to be within a predetermined Range.

3. The predetermined Ranges that can be selected are:
5-10, 11-15, 16-20, 21-25, 26-30 and 31-34. This is the
difference between the two numbers - the highest and
lowest Randomly selected numbers.

4. Formula to check that Randomly selected numbers meet
criteria.

Example:
Predetermined Range required 5-10
Numbers Randomly Selected 18 and 28
28-18=10 therefore within predetermined Range of 5-10

5. Need option to add a further three numbers Randomly
and option to Self Select: adding to the two previous
numbers that have met the Range criteria. These three
numbers to be placed in ascending order between the two
previous criteria satisfied highest and lowest. The three
numbers slotted in between must not be lower than the
previous criteria Range Lowest and likewise not higher
than the previous criteria Range Highest.

Example:
18 - - - 28
18 20 21 26 28

6. All five numbers to be displayed and sorted in
ascending order, left to right.

7. VBA to produce a display of 10 sets of 5 numbers
meeting the criteria.

Does anyone have the time to code a solution?

Appreciated.

Cheers
Tina

 

[Ken Macksey]

I have a solution for you.
Email me and I will send it to you.

Ken

 

[Tina]

Hi Ken,

Thanks for file. I'll still need to post for further
assistance.

The main difference in what your file does and what I'm
actually trying to do is that the Ranges in your file
directly reflect the actual numbers associated with the
Ranges; eg: Range 5-10 selects numbers 5, 6, 7, 8, 9, 10.

Whereas my Range representation means the subtraction of
the lowest Random number from the highest Random number
to fall within the Range of 5-10; example:

18 - - - 28, Subtract 18 from 28, 28-18=10; thus falling
within the Range 5-10 not actually selecting numbers 5,
6, 7, 8, 9, or 10.

However, thank you for reply and assistance. If any
other solution ideas spring to mind please let me know.

Thanks
Tina

 

[ste mac]

Hi Tina...

Are you looking at the UK Thunderball?

ste

 

[Tina]

Hi Ste,

Yes, I am looking at the UK Thunderball; can you help?

Tina

 

[Sandy Mann]

Tina,

This may not be what you want - but it may give you some ideas or a basis to
construct your own sheet..

Enter one of your lower predetermined range values in D1 and in E1 the
formula

=D1+3+(D1<>31)+(D1=5)

Now in G2 enter:
=IF(E1=34,ROUNDUP(RAND()*5+31,0),ROUNDUP(E1+RAND()*(34-E1),0))

and in C2 enter:
=MAX(1,ROUND((G2-E1)+RAND()*(E1-D1),0))
note that this is ROUND not ROUNDUP

finally in D2 enter
=ROUNDUP(RAND()*($G$2-C2-(5-(COLUMN()-COLUMN($C$2))))+C2,0)
and copy it along to F2

Repeated pressing of Key F9 will give numbers within the ranges you
specified.

HTH

Sandy

 

[Tina]

Hi Sandy,

Thank you very much for taking the time and effort to
provide formula. Extremely useful.

Cheers
Tina

 

[Sandy Mann]

You're welcome. It was not exactly what you wanted but I hope that it gives
you enough information to build the spreadsheet that you wanted.

Regards

Sandy