Share via

How to interpret formulas of the form {=TABLE(,A3)} ?

Anonymous
2021-12-07T21:10:10+00:00

(Sigh, I feel like I've asked this question some time in the past. But I can't find a record of it.)

Download the file "table formula.xlsx" (click here). See the "sim1" and "sim2" worksheets.

What do the formulas {=TABLE(,A3)} and {=TABLE(,A4)} do?

How were they entered?

If an Excel "widget" was used (e.g. Data > What If Analysis > Data Table), with what parameters?

More to the point: can someone provide a "normal" formula that accomplishes the same thing?

(FYI, I received the file from a user that I'm helping. Due to timezone differences, I cannot get a timely explanation from him. So I have no knowledge of the intent of the formulas. Hopefully, my questions can be answered without having to know that.)

PS.... If it helps answer my other questions, I think the following is a "normal" formula that accomplishes a similar thing, starting in sim1!C12:

=MAX(0, ROUND(VLOOKUP(RAND(),'prob distrib'!$C$4:$D$723,2,TRUE) + NORM.INV(RAND(),$C$5,$C$6), 0))

And a similar concept (but a very different formula) starting in sim2!C19.

(Note: I added MAX(0,...). It should be in G6, IMHO.)

Unfortunately, we cannot make a direct comparison because of the need to recalculate RAND in each row.

My guess: that is accomplished automagically with the TABLE reference. Right? Can someone explain that a little better?

Be that as it may, I still do not understand the TABLE reference -- my first 3 questions. What it references and how? And how it operates (recalculating RAND in each row?)?

So I'm still looking for some insight into them.

Microsoft 365 and Office | Excel | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2021-12-08T19:08:57+00:00

Hi. My best guess:

a) With C12 having the formula: =G6

   Select B12:C755

   Then What-IF, Data Table, and in 

   In the Column Input Cell, type A3.

b) I "think" the formula is always returning the values in G6. It changes the value based on the formula in A3. However, A3 is blank, and not really contributing to the formula. The returned value is therefore basically relying on RAND() to change the values each time.

So I think that if C6:D19 are blank, and one enters a formula in D6, one then

Selects C6:D19, then Data Table. The inputs from Column C (blank here) are feed into B3.

However, B3 is not part of the formula in B6. Therefore, Rand() is the only function causing the different values.

In other words, B3 is the input for each of the cells in C7:C19 (that don't happen to have any affect.)

Hope I said that correctly.

Was this answer helpful?

1 person found this answer helpful.
0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-12-08T14:31:26+00:00

    > ... VLOOKUP(RAND(),'prob distrib'!$C$4:$D$723,2,TRUE)

    If I am not mistaken, and they have M365, perhaps they can eliminate the

    "discrete prob" and "cuml prob" columns, and just use:

    =INDEX(data, RANDBETWEEN(1,720))

    Was this answer helpful?

    0 comments
  2. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2021-12-08T04:58:02+00:00

    You are welcome.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-12-08T03:49:46+00:00

    Thanks. With that design paradigm, the performance of the larger Excel file is very poor. We can actually see the sources of the Date Table being recalculated iteratively (over 700 times in 4 worksheets) each time I open the file or edit it. It works better when I remove the Data Table references and use the "normal" formula (or something similar) that I mentioned instead. In any case, I'm no longer interested in the particulars. I tried to delete the thread, but the forum doesn't allow it after there has been at least one response.

    Was this answer helpful?

    0 comments
  4. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2021-12-08T00:12:01+00:00

    Hi,

    That Table array formula gets auto generated when you create a Data Table from Data > What If Analysis > Data Table. It cannot be manually entered. {=TABLE(,A3)} means that the row input cell was left blank.

    Was this answer helpful?

    0 comments