Solved

What does this formula mean in Excel?

Posted on 2016-10-21
4
36 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 45

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 81

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 14

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

738 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