Solved

What does this formula mean in Excel?

Posted on 2016-10-21
4
24 Views
Last Modified: 2016-11-27
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
Comment
Question by:ResourcefulDB
4 Comments
 
LVL 41

Expert Comment

by:Jackie Man
ID: 41854538
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
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 41855714
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
 

Author Comment

by:ResourcefulDB
ID: 41864048
thanks.
0
 
LVL 13

Expert Comment

by:frankhelk
ID: 41902935
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Outlook Free & Paid Tools
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now