Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 73
  • Last Modified:

What does this formula mean in Excel?

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
0
ResourcefulDB
Asked:
ResourcefulDB
1 Solution
 
Jackie ManCommented:
The formula was created by the Data | Table menu option. This option lets you create a "what-if" table that shows the result of varying one or two arguments in a formula. In this example, only one argument varies.

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.
0
 
byundtCommented:
The RANDBETWEEN formulas in cells C4:AZ6 simulate the random roll of a die, and recalculate when a worksheet value is changed or the user hits F9. Cells AZ8 and C10 determine the Win/Lose and number of rerolls needed for 50 rolls of three dice.

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.
0
 
ResourcefulDBAuthor Commented:
thanks.
0
 
frankhelkCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now