It seems that the formula is an array formula. But I do not know what it does exactly.

H16=TABLE(,A1)

if you click on the H16, it will appear as {=TABLE(,A1)}. Same as I16 and the following 4999 cells. It seems that line 16 and on are records of monte carlo results.

Table() is not a buildin function. So, what trick is this?

Thanks, RDB

Monte-Carlo-Worked-Answer.xlsx

H16=TABLE(,A1)

if you click on the H16, it will appear as {=TABLE(,A1)}. Same as I16 and the following 4999 cells. It seems that line 16 and on are records of monte carlo results.

Table() is not a buildin function. So, what trick is this?

Thanks, RDB

Monte-Carlo-Worked-Answer.xlsx

Source: http://windowssecrets.com/forums/showthread.php/99363-TABLE-function-in-array-formula-help-decipher-p-(Excel-2002)

It is a typical data analysis function of MS Excel.

I have recommended this question be closed as follows:

Accept: byundt (https:#a41855714)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

frankhelk

Experts-Exchange Cleanup Volunteer

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

In a Monte Carlo simulation, you want to repeat the above experiment numerous times, and then track the results. The one-input data table (as mentioned by Jackie Man) does just that.

You can produce a one-input data table by selecting a rectangular range of cells with input values in the leftmost column and formulas at the top of columns to the right. Having done that, you would then use the Data ribbon...What-If Analysis...Data table menu item. The only confusing part about this process is remembering that the Row input field should remain blank and the Column input field should point to a single cell in the worksheet where the column of values will be plugged in. That's why the funny array formula reads {=TABLE(,A1)} -- the first parameter (Row input field) isn't being used, and the second parameter (Column input field) is bringing the column of values one at a time to cell A1.

I like to think of a data table as plugging a column of values into a single cell in a "calculation engine" section of a worksheet, then using the data table to tabulate values from the "results" section of the worksheet. In this particular workbook, the numbers 1 through 5000 (in cells G16:G5015) are successively placed in cell A1. The "calculation engine" in C8:AZ10 chugs away simulating the rolls of the dice. After each successive number is plugged in, the formulas at the top of the data table (H15:I15) capture the values from the "results" section (cells AZ8 and C10) and the data table tabulates those results in columns H and I.

You may note that cell A1 isn't used by any of the formulas in the workbook. That is OK--because plugging a number into that cell is sufficient to make the "calculation engine" (RANDBETWEEN formulas in C4:AZ6) recalculate.