How to make many random test outcomes in one chart?

  • Thread starter Thread starter vegas
  • Start date Start date
V

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top