Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
vegas
 
Posts: n/a
Default How to make many random test outcomes in one chart?


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

  #2   Report Post  
Biff
 
Posts: n/a
Default

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" wrote in
message ...

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



  #3   Report Post  
Max
 
Posts: n/a
Default

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 <atyahoo<dotcom
----
"vegas" wrote in
message ...

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



  #4   Report Post  
vegas
 
Posts: n/a
Default


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


--
vegas
------------------------------------------------------------------------
vegas's Profile: http://www.excelforum.com/member.php...o&userid=25091
View this thread: http://www.excelforum.com/showthread...hreadid=386025

  #5   Report Post  
Max
 
Posts: n/a
Default

You're welcome, vegas !
Glad to hear that ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"vegas" wrote in
message ...

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


--
vegas



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pie-in-Pie (sort of) how can I make this chart? KR Charts and Charting in Excel 1 June 10th 05 12:35 PM
Urgent Chart Assistance Brent E Charts and Charting in Excel 1 May 10th 05 09:09 AM
Urgent Chart Questions Brent E Excel Discussion (Misc queries) 0 May 9th 05 11:01 PM
Urgent Chart Assistance Requested Brent E Excel Discussion (Misc queries) 0 May 9th 05 11:01 PM
How to make a stacked bar chart using time? Jon Peltier Charts and Charting in Excel 0 February 3rd 05 06:56 AM


All times are GMT +1. The time now is 10:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"