Average of Generated Data Results

[Guest]

Hello, I have a complicated model that spits out its results into one cell "Total Profit". Parts of the model use the RAND() function so if you hit f9 the result will change.

Trying to logically think thru this, I have to hit f9 300 times, record each result (300 rows of record), and then average those 300 results.

There has to be some way to automate this?? (There will end up being 300 rows of data that will change each time I hit f9, so this ending average will prove that the model is reliable...)

 

[Max]

Try this set-up (using a 1 variable data table):

Set calculation mode to "Manual" first

(Click Tools > Options > Calculation tab
Check "Manual", uncheck "Recalculate before save" > OK)

Assume the "Total Profit" is in cell A3

In any 2 empty columns to the right,
let's say cols D & E

Fill the numbers 1 to 300 down D3302

Put in E1: =AVERAGE(E3:E302)

Put in E2: =A3 (the "Total Profit" cell)
(mask E2 by formatting the font > white)

Select D2:E302

Click Data > Table

Leave row input cell blank

Put in column input cell: $D$1 (ie any empty cell)

Click OK

Press F9 to recalculate

When recalculation completes, you will have
300 iterations of "Total Profit" in E3:E302
with the average in E1

Just select say E1:E302
and copy > paste special > values elsewhere
to freeze the 300 iteration values & the average

Pressing F9 will generate another 300 runs

--
hth
Max
-----------------------------------------
Please reply in thread

Use xdemechanik <at>yahoo<dot>com for email
----------------------------------------------------------------------------

Hello, I have a complicated model that spits out its results into one cell

"Total Profit". Parts of the model use the RAND() function so if you hit f9
the result will change.

Trying to logically think thru this, I have to hit f9 300 times, record

each result (300 rows of record), and then average those 300 results.

There has to be some way to automate this?? (There will end up being 300

rows of data that will change each time I hit f9, so this ending average
will prove that the model is reliable...)

 

[Max]

Try this set-up (using a 1 variable data table):

Set calculation mode to "Manual" first

(Click Tools > Options > Calculation tab
Check "Manual", uncheck "Recalculate before save" > OK)

Assume the "Total Profit" is in cell A3

In any 2 empty columns to the right,
let's say cols D & E

Fill the numbers 1 to 300 down D3302

Put in E1: =AVERAGE(E3:E302)

Put in E2: =A3 (the "Total Profit" cell)
(mask E2 by formatting the font > white)

Select D2:E302

Click Data > Table

Leave row input cell blank

Put in column input cell: $D$1 (ie any empty cell)

Click OK

Press F9 to recalculate

When recalculation completes, you will have
300 iterations of "Total Profit" in E3:E302
with the average in E1

Just select say E1:E302
and copy > paste special > values elsewhere
to freeze the 300 iteration values & the average

Pressing F9 will generate another 300 runs

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik <at>yahoo<dot>com for email
--------------------------------------------------

Hello, I have a complicated model that spits out its results into one cell

"Total Profit". Parts of the model use the RAND() function so if you hit f9
the result will change.

Trying to logically think thru this, I have to hit f9 300 times, record

each result (300 rows of record), and then average those 300 results.

There has to be some way to automate this?? (There will end up being 300

rows of data that will change each time I hit f9, so this ending average
will prove that the model is reliable...)