Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pie-in-Pie (sort of) how can I make this chart? | Charts and Charting in Excel | |||
Urgent Chart Assistance | Charts and Charting in Excel | |||
Urgent Chart Questions | Excel Discussion (Misc queries) | |||
Urgent Chart Assistance Requested | Excel Discussion (Misc queries) | |||
How to make a stacked bar chart using time? | Charts and Charting in Excel |