How to make many random test outcomes in one chart?

[vegas]

For Example,
From column A1 to A20 I assign 20 random numbers (A1=rand(), A2=rand(), ... A20=rand()).
Then I put B1=sum(A1,A2,...,A20)
Every time I press "F9", I got a outcome of B1
Now I want to get the outcome 1000 times, and assign the data to other columns such as C11 to C10000. How to do that?

[Biff]

Hi!

Here's one way....

Extend the RAND formula in column A down to A20000

Hit function key F5 and enter A1:A20000
Click OK

In the formula bar type in =RAND() and hit CTRL ENTER

That will put the RAND formula in the range A1:A20000

Hit F5 again and enter C1:C1000
Click OK

In the formula bar type in this formula and then hit CTRL ENTER

=SUM(OFFSET(A$1,(ROW()-1)*20,,20))

That will put 1000 random sums of 20 cells each in the range C1:C1000

Biff

"vegas" <[email protected]> wrote in
message news:[email protected]...
>
> For Example,
> From column A1 to A20 I assign 20 random numbers (A1=rand(), A2=rand(),
> .. A20=rand()).
> Then I put B1=sum(A1,A2,...,A20)
> Every time I press "F9", I got a outcome of B1
> Now I want to get the outcome 1000 times, and assign the data to other
> columns such as C11 to C10000. How to do that?
>
>
> --
> vegas
> ------------------------------------------------------------------------
> vegas's Profile:
> http://www.excelforum.com/member.php...o&userid=25091
> View this thread: http://www.excelforum.com/showthread...hreadid=386025
>

[Max]

A one variable data table is quite ideal for this ..

You have the formula in B1: =SUM(A1:A20)
(B1 is the output cell of interest)

Put in C10: =B1
(just a simple link to the output cell)

Fill* the numbers 1,2,3, ... into B11:B10000,
i.e. into a range just to the left of the output range in C11:C10000
*Put in B11: 1, in B12: 2, then select B11:B12 and fill down to B10000

Select B10:C10000

Click Data > Table
Leave the "Row input cell" box empty
Put in Column input cell: B10 (say*)
Click OK

*Can be any empty cell in the vicinity of the selected range, e.g.: B9

In C11:C10000 will be computed 9,990 randomized results for B1

Each press of F9 will generate afresh another 9,990 sets

Freeze the results elsewhere via a copy > paste special > values

Adapt to suit ..

Note that although the TABLE function (e.g.: {=TABLE(,B10)} ) will appear in
every cell in C11:C10000 and looks very much like an array formula, it
cannot just be entered as such. The construct of the data table must be
done / invoked via the Data > Table menu / steps outlined

And as data tables are calc-intensive, you might also want to switch the
calc mode from "Automatic" to either "Automatic except tables" or "Manual"
(via checking the option in: Tools > Options > Calculation tab)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"vegas" <[email protected]> wrote in
message news:[email protected]...
>
> For Example,
> From column A1 to A20 I assign 20 random numbers (A1=rand(), A2=rand(),
> .. A20=rand()).
> Then I put B1=sum(A1,A2,...,A20)
> Every time I press "F9", I got a outcome of B1
> Now I want to get the outcome 1000 times, and assign the data to other
> columns such as C11 to C10000. How to do that?
>
>
> --
> vegas
> ------------------------------------------------------------------------
> vegas's Profile: http://www.excelforum.com/member.php...=getinfo&useri
d=25091
> View this thread: http://www.excelforum.com/showthread...hreadid=386025
>

[vegas]

HI Max & Biff, both of the approaches works, thank you both!

[Max]

You're welcome, vegas !
Glad to hear that ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"vegas" <[email protected]> wrote in
message news:[email protected]...
>
> HI Max & Biff, both of the approaches works, thank you both!
>
>
> --
> vegas