random numbers

I am trying to generate a list of random numbers based on the results in a
couple cells.

Cell C3 contains a number that is input by the user.

Cell C5 contains the results of a formula - related to the number in C3

I would like to create X number of random numbers, where X = C5, and all of
the random numbers must be integers between 1 and the value in cell C3

I've tried a few things based on some other posts, and using some sorting,
but haven't been able to do this successfully. Any help / suggestions would
be appreciated.

Thanks.

Lars-Åke
Tue Jul 22 13:10:44 CDT 2008

On Tue, 22 Jul 2008 10:41:04 -0700, tomic
<tomic@discussions.microsoft.com> wrote:

>I am trying to generate a list of random numbers based on the results in a
>couple cells.
>
>Cell C3 contains a number that is input by the user.
>
>Cell C5 contains the results of a formula - related to the number in C3
>
>I would like to create X number of random numbers, where X = C5, and all of
>the random numbers must be integers between 1 and the value in cell C3
>
>I've tried a few things based on some other posts, and using some sorting,
>but haven't been able to do this successfully. Any help / suggestions would
>be appreciated.
>
>Thanks.

Assuming that you want your random numbers in the D column, try the
following formula in Cell D1:

=IF(ROW()<=C$5,RANDBETWEEN(1,C$3),"")

Copy this down as long as needed to cover the maximum number of random
numbers that might be relevant.

Hope this helps / Lars-Åke

GarysStudent
Tue Jul 22 13:19:01 CDT 2008

In D1 enter:

=IF(ROW()>C$5,"",RANDBETWEEN(1,C$3)) and copy down
--
Gary''s Student - gsnu200795


"tomic" wrote:

> I am trying to generate a list of random numbers based on the results in a
> couple cells.
>
> Cell C3 contains a number that is input by the user.
>
> Cell C5 contains the results of a formula - related to the number in C3
>
> I would like to create X number of random numbers, where X = C5, and all of
> the random numbers must be integers between 1 and the value in cell C3
>
> I've tried a few things based on some other posts, and using some sorting,
> but haven't been able to do this successfully. Any help / suggestions would
> be appreciated.
>
> Thanks.

gary
Tue Jul 22 13:23:00 CDT 2008

'/=============================/
Sub Macro1()
Dim dbl As Double
Dim dblMax As Double
Dim dblRandNos As Double

'put random #s in row starting at Cell D1
Range("D1").Select

'clear Column D of old info
Range("D:D").ClearContents

'get values in C3 and C5
dblMax = Range("C3").Value2
dblRandNos = Range("C5").Value2

'create random #s
For dbl = 1 To dblRandNos
ActiveCell.Offset(dbl - 1, 0).Value = _
Int(Rnd() * dblMax) + 1
Next dbl

End Sub
'/=============================/

--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"tomic" wrote:

> I am trying to generate a list of random numbers based on the results in a
> couple cells.
>
> Cell C3 contains a number that is input by the user.
>
> Cell C5 contains the results of a formula - related to the number in C3
>
> I would like to create X number of random numbers, where X = C5, and all of
> the random numbers must be integers between 1 and the value in cell C3
>
> I've tried a few things based on some other posts, and using some sorting,
> but haven't been able to do this successfully. Any help / suggestions would
> be appreciated.
>
> Thanks.

tomic
Tue Jul 22 14:14:02 CDT 2008

Thanks Gary.

Works Great.

The other responses look easy too...however, I got an unrecognized name
error. I'm using Excel 2003 - is randbetween a formula in 2007?



"Gary Brown" wrote:

> '/=============================/
> Sub Macro1()
> Dim dbl As Double
> Dim dblMax As Double
> Dim dblRandNos As Double
>
> 'put random #s in row starting at Cell D1
> Range("D1").Select
>
> 'clear Column D of old info
> Range("D:D").ClearContents
>
> 'get values in C3 and C5
> dblMax = Range("C3").Value2
> dblRandNos = Range("C5").Value2
>
> 'create random #s
> For dbl = 1 To dblRandNos
> ActiveCell.Offset(dbl - 1, 0).Value = _
> Int(Rnd() * dblMax) + 1
> Next dbl
>
> End Sub
> '/=============================/
>
> --
> Hope this helps.
> Thanks in advance for your feedback.
> Gary Brown
>
>
> "tomic" wrote:
>
> > I am trying to generate a list of random numbers based on the results in a
> > couple cells.
> >
> > Cell C3 contains a number that is input by the user.
> >
> > Cell C5 contains the results of a formula - related to the number in C3
> >
> > I would like to create X number of random numbers, where X = C5, and all of
> > the random numbers must be integers between 1 and the value in cell C3
> >
> > I've tried a few things based on some other posts, and using some sorting,
> > but haven't been able to do this successfully. Any help / suggestions would
> > be appreciated.
> >
> > Thanks.

Rick
Tue Jul 22 14:22:56 CDT 2008

The RANDBETWEEN function requires the Analysis ToolPak add-in to be loaded
(click Tools/Add-Ins on Excel's menu bar).

Rick


"tomic" <tomic@discussions.microsoft.com> wrote in message
news:67715C75-2158-4318-9069-7369B1608C99@microsoft.com...
> Thanks Gary.
>
> Works Great.
>
> The other responses look easy too...however, I got an unrecognized name
> error. I'm using Excel 2003 - is randbetween a formula in 2007?
>
>
>
> "Gary Brown" wrote:
>
>> '/=============================/
>> Sub Macro1()
>> Dim dbl As Double
>> Dim dblMax As Double
>> Dim dblRandNos As Double
>>
>> 'put random #s in row starting at Cell D1
>> Range("D1").Select
>>
>> 'clear Column D of old info
>> Range("D:D").ClearContents
>>
>> 'get values in C3 and C5
>> dblMax = Range("C3").Value2
>> dblRandNos = Range("C5").Value2
>>
>> 'create random #s
>> For dbl = 1 To dblRandNos
>> ActiveCell.Offset(dbl - 1, 0).Value = _
>> Int(Rnd() * dblMax) + 1
>> Next dbl
>>
>> End Sub
>> '/=============================/
>>
>> --
>> Hope this helps.
>> Thanks in advance for your feedback.
>> Gary Brown
>>
>>
>> "tomic" wrote:
>>
>> > I am trying to generate a list of random numbers based on the results
>> > in a
>> > couple cells.
>> >
>> > Cell C3 contains a number that is input by the user.
>> >
>> > Cell C5 contains the results of a formula - related to the number in C3
>> >
>> > I would like to create X number of random numbers, where X = C5, and
>> > all of
>> > the random numbers must be integers between 1 and the value in cell C3
>> >
>> > I've tried a few things based on some other posts, and using some
>> > sorting,
>> > but haven't been able to do this successfully. Any help / suggestions
>> > would
>> > be appreciated.
>> >
>> > Thanks.

GarysStudent
Tue Jul 22 14:34:05 CDT 2008

Tools > Add-Ins... > make sure Analysis ToolPak is checked
--
Gary''s Student - gsnu200795


"tomic" wrote:

> Thanks Gary.
>
> Works Great.
>
> The other responses look easy too...however, I got an unrecognized name
> error. I'm using Excel 2003 - is randbetween a formula in 2007?
>
>
>
> "Gary Brown" wrote:
>
> > '/=============================/
> > Sub Macro1()
> > Dim dbl As Double
> > Dim dblMax As Double
> > Dim dblRandNos As Double
> >
> > 'put random #s in row starting at Cell D1
> > Range("D1").Select
> >
> > 'clear Column D of old info
> > Range("D:D").ClearContents
> >
> > 'get values in C3 and C5
> > dblMax = Range("C3").Value2
> > dblRandNos = Range("C5").Value2
> >
> > 'create random #s
> > For dbl = 1 To dblRandNos
> > ActiveCell.Offset(dbl - 1, 0).Value = _
> > Int(Rnd() * dblMax) + 1
> > Next dbl
> >
> > End Sub
> > '/=============================/
> >
> > --
> > Hope this helps.
> > Thanks in advance for your feedback.
> > Gary Brown
> >
> >
> > "tomic" wrote:
> >
> > > I am trying to generate a list of random numbers based on the results in a
> > > couple cells.
> > >
> > > Cell C3 contains a number that is input by the user.
> > >
> > > Cell C5 contains the results of a formula - related to the number in C3
> > >
> > > I would like to create X number of random numbers, where X = C5, and all of
> > > the random numbers must be integers between 1 and the value in cell C3
> > >
> > > I've tried a few things based on some other posts, and using some sorting,
> > > but haven't been able to do this successfully. Any help / suggestions would
> > > be appreciated.
> > >
> > > Thanks.

tomic
Tue Jul 22 14:40:15 CDT 2008

Thanks.

"Gary''s Student" wrote:

> Tools > Add-Ins... > make sure Analysis ToolPak is checked
> --
> Gary''s Student - gsnu200795
>
>
> "tomic" wrote:
>
> > Thanks Gary.
> >
> > Works Great.
> >
> > The other responses look easy too...however, I got an unrecognized name
> > error. I'm using Excel 2003 - is randbetween a formula in 2007?
> >
> >
> >
> > "Gary Brown" wrote:
> >
> > > '/=============================/
> > > Sub Macro1()
> > > Dim dbl As Double
> > > Dim dblMax As Double
> > > Dim dblRandNos As Double
> > >
> > > 'put random #s in row starting at Cell D1
> > > Range("D1").Select
> > >
> > > 'clear Column D of old info
> > > Range("D:D").ClearContents
> > >
> > > 'get values in C3 and C5
> > > dblMax = Range("C3").Value2
> > > dblRandNos = Range("C5").Value2
> > >
> > > 'create random #s
> > > For dbl = 1 To dblRandNos
> > > ActiveCell.Offset(dbl - 1, 0).Value = _
> > > Int(Rnd() * dblMax) + 1
> > > Next dbl
> > >
> > > End Sub
> > > '/=============================/
> > >
> > > --
> > > Hope this helps.
> > > Thanks in advance for your feedback.
> > > Gary Brown
> > >
> > >
> > > "tomic" wrote:
> > >
> > > > I am trying to generate a list of random numbers based on the results in a
> > > > couple cells.
> > > >
> > > > Cell C3 contains a number that is input by the user.
> > > >
> > > > Cell C5 contains the results of a formula - related to the number in C3
> > > >
> > > > I would like to create X number of random numbers, where X = C5, and all of
> > > > the random numbers must be integers between 1 and the value in cell C3
> > > >
> > > > I've tried a few things based on some other posts, and using some sorting,
> > > > but haven't been able to do this successfully. Any help / suggestions would
> > > > be appreciated.
> > > >
> > > > Thanks.