# Random numbers!

#### alexjmatthews

##### New Member
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

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
If your data begins in A1, and there is no other data in column A, =OFFSET(A1,ROUND((COUNT(A:A)-1)*RAND(),0),)

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))

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

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

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

For me, I prefer INDEX because it's not a volatile function like OFFSET
Granted. That may be persuasive enough.

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.

Appreciated

Replies
1
Views
157
Replies
6
Views
331
Replies
4
Views
215
Replies
3
Views
697
Replies
6
Views
202

1,219,820
Messages
6,150,408
Members
450,960
Latest member
GB2

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back