[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

What does this formula mean in Excel?

Posted on 2016-10-21
4
Medium Priority
?
52 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 49

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 2000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

649 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