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


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=389443

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

"STEVEB" wrote:


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


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=389443

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


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=389443

"STEVEB" wrote
.. 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 B1:D1, 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:D,ROWS($A$1 :A1)),Sheet1!D: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,COLUM NS($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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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" wrote:


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


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=389443

Thanks Max,

Everything worked Great! I really appreciate your Help!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=389443

You're welcome !
Appreciate the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"STEVEB" wrote in
message ...

Thanks Max,

Everything worked Great! I really appreciate your Help!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=389443