Random Number Generator

[STEVEB]

Hi,

Does anyone have any suggestions for a Random Number Generator that
would only return weekdays?

This is my current formula (which works great but generates Saturday &
Sunday days):


=RAND()*($F$4-$F$3)+$F$3

F3= 38,353
F4= 38,564

Any help would be greatly appreciated!

Thanks

 

[bj]

try
= 38352+randbetween(1,5)+7*rand between(0,30)

 

[STEVEB]

dj,

Thanks for your help.

I am still getting Saturday & Sunday. Have I written the formula
correctly?

=38352+RANDBETWEEN(1,5)+7*RANDBETWEEN(0,30)

Thanks

 

[Max]

.. suggestions for a Random Number Generator that
would only return weekdays?

Here's one play which will return only random weekdays from within a defined
period (Start date to End date) ..

In Sheet1
------
Put the start date in A1: 01-Jan-2005
Copy A1 down to A212, to fill the range till the end date in A212:
31-Jul-2005

Put

in B1:
=IF(OR(WEEKDAY(A1,2)={6,7}),"",ROW())

in C1:
=INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1:A1)),B:B,0))

in D1:
=IF(ISERROR(C1),"",RAND())

Select B11, fill down to D212

Col B is an arb. col which assigns row numbers for weekdays within A1:A212.
Col C extracts the weekdays from col A as marked in col B to the top
Col D will generate the underlying randomization for use

---
Then, in any new sheet, say, in Sheet2
-----
a. To generate random weekdays down a column

Put in any starting cell, say A2:
=INDEX(Sheet1!C:C,MATCH(SMALL(Sheet1!D,ROWS($A$1:A1)),Sheet1!D,0))

Format A2 as date and copy A2 down
(can copy down a max of 150* rows)

b. To generate random weekdays across a row

Put in any starting cell, say, C1
=INDEX(Sheet1!$C:$C,MATCH(SMALL(Sheet1!$D:$D,COLUMNS($A$1:A1)),Sheet1!$D:$D,
0))

Format C1 as date and copy C1 across
(can copy across a max of 150* cols)

Each press of the F9 key will regenerate the random weekdays

*If you look at the outputs in Sheet1, there's only 150 weekdays within the
defined period.

 

[Max]

Format A2 as date and copy A2 down

(can copy down a max of 150* rows)

Format C1 as date and copy C1 across
(can copy across a max of 150* cols)

Just a clarification that the random weekdays generated within the (max) 150
cells filled down/across will be non-repeating random weekdays from within
the 150 weekdays in the source range (col C in Sheet1)

 

[bj]

I screwed up use
=38354+RANDBETWEEN(1,5)+7*RANDBETWEEN(0,30)
I meant to start with a sunday and I started with a friday.

the

 

[STEVEB]

Thanks Max,

Everything worked Great! I really appreciate your Help

 

[Max]

You're welcome !
Appreciate the feedback