Pause for thought!


J

JOHN BERNARD

Col A1=0, A2=2 etc down to A91=90

Col B1=RAND() etc down to B91=RAND()

D1=INDEX($A$2:$A$92,RANK(B2,$B$2:$B$92))

I press F9 until 0(zero) appears (in D1) then:

I press F9 (once) and a single random number (1-90) appears (in D1)

A normal, commercial, random selector (when pressing the start button)
visually revolves through all numbers (for several seconds) before stopping,
having selected a number.

Question: What do I need to add to D1 (above) to make it do the same?

I know it can be done, but it has got me beaten. Please advise.
 
Ad

Advertisements

N

Nigel

What you appear to want is a random number between 0 and 91, assuming you
meant A2=1 (not 2 as in your post); otherwise A91 would not equal 90.

In Excel 2007 you could use...

=RANDBETWEEN(0,91)

In earlier versions use

=INT(RAND()*(92))

If you want numbers to 'roll' around for some visual effect then you need
some VBA code to control the output. Excel does not have this function built
in. Try something like...and assign this macro SpinNumber to a worksheet
control.

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub SpinNumber()
Dim iX As Integer
For iX = 1 To 200
Range("D1") = Int(92 * Rnd)
Sleep 10
Next
End Sub
 
Ad

Advertisements

J

JOHN BERNARD

Thanks Nigel. Yes - A2 = 1.

Not sure about the 'roll' around macro, but will give it a try.
Much appreciated
--
Regards,
John


Nigel said:
What you appear to want is a random number between 0 and 91, assuming you
meant A2=1 (not 2 as in your post); otherwise A91 would not equal 90.

In Excel 2007 you could use...

=RANDBETWEEN(0,91)

In earlier versions use

=INT(RAND()*(92))

If you want numbers to 'roll' around for some visual effect then you need
some VBA code to control the output. Excel does not have this function built
in. Try something like...and assign this macro SpinNumber to a worksheet
control.

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub SpinNumber()
Dim iX As Integer
For iX = 1 To 200
Range("D1") = Int(92 * Rnd)
Sleep 10
Next
End Sub

--

Regards,
Nigel
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top