Random numbers!

[alexjmatthews]

Could someone post how to get excel to randomly select one of the following times:

08:00
00:00
02:05
04:00
12:00
14:00
16:00
18:00
20:00

Cheers for your help!

 

[Gates Is Antichrist]

If your data begins in A1, and there is no other data in column A, =OFFSET(A1,ROUND((COUNT(A:A)-1)*RAND(),0),)

 

[Scott Huish]

If you have the Analysis Toolpak installed, perhaps:

=INDEX(A:A,RANDBETWEEN(1,COUNT(A:A)))

or possibly:
=INDEX(A:A,INT(RAND()*COUNT(A:A)+1))

 

[barry houdini]

If your range is A1:A9

=INDEX(A1:A9,RAND()*ROWS(A1:A9)+1)

 

[Gates Is Antichrist]

Why would one use INDEX instead of OFFSET? One-based logic is feeb! Offset is zero based - like God intended!

I acknowledge that, if I'm not mistaken, there an overwhelming preference for INDEX throughout the lands; God himself would lash forth with a syntax error if anyone ever deviated from the popular solution (index(match... . So everyone does it, except me. What am I missing here?!

 

[barry houdini]

Why would one use INDEX instead of OFFSET?

For me, I prefer INDEX because it's not a volatile function like OFFSET

Note: the formula

=OFFSET(A1,ROUND((COUNT(A:A)-1)*RAND(),0),)

won't give an even distribution, you'll get less instances of the first and last times in the column

 

[Gates Is Antichrist]

For me, I prefer INDEX because it's not a volatile function like OFFSET

Granted. That may be persuasive enough.

 

[Gates Is Antichrist]

Note: the formula

=OFFSET(A1,ROUND((COUNT(A:A)-1)*RAND(),0),)

won't give an even distribution, you'll get less instances of the first and last times in the column

Oops! Got me there! /self-brain-pummel-with-hammer mode on.

 

[alexjmatthews]

Cheers for your help guys!

Appreciated