Extract formula from Excel Data Table (What-If Analysis)

Question

I am faced with rewriting an Excel project in R. I see a table in which a cell {= TABLE (F2, C2)} is shown. I understand how to create a Table like this (What-If Analysis, Data Table...).

As I have to understand this to rewrite in R, how can I find the original formula which stands behind that cell?

EXAMPLE: I have created a Data Table as shown here and the sheet looks like this:

In my case, I don't know how the sheet was created, and I want to know the initial formula. Now this is shown as {=TABLE(,C4)}. (In the example I know the answer, it is in the cell (D10), but where is reference for this cell in Data Table?)

Answer 1

I'm using Excel 2007 but have no reason to believe things differ in other versions.

@Stanislav was right to reject my comment suggestion that TABLE was a name; it is an EXCEL function. But it is a very strange function :-}

• There isn't any help on the TABLE function in the local help, it isn't listed in "List of worksheet functions (alphabetical)".
• You can't manually enter or edit the TABLE function; error "That function is not valid".
• Copy/Pasting cells containing the TABLE function pastes their values, not their formulae, even when you specify Paste Special > Formulas
• You can't insert rows/columns immediately above/left of cells containing the TABLE function; error "Cannot change part of a data table".
• Pace @pnuts using Formulas > Formula Auditing cells containing the TABLE function shows no precedents and no cells show them as dependents. Although in a VBA sheet auditing tool which I use the Range.DirectDependents Property finds the "formula range" dependent on the "margin" cells containing the formulas, but not those containg the values (see below for explanation of those terms).

I haven't been able to find anything I regard as decent documentation of TABLE(). I have found lots of illustrations of how to produce and use that function, but nothing clearly specifying the arguments and result. The best I've found is https://support.office.com/en-us/article/Calculate-multiple-results-by-using-a-data-table-e95e2487-6ca6-4413-ad12-77542a5ea50b. I'd be pleased if anyone can point me to better documentation.

I deduce the bahaviour as described here:

TABLE(Rowinp,Colinp) is an array formula in a contiguous array of cells. I'll refer to that contiguous array as the "formula range" of the data table. The cells immediately above/left of the formula range are also part of the data table, even though they do not contain a TABLE() function and can be edited; I'll refer to those cells as the "margins" of the data table.

Rowinp and Colinp must be blank or references to single cells. Rowinp and Colinp must be different (or error "Input cell reference is not valid"), they must not both be blank.

The values in the formula range are calculated by taking formula(s) from the margin(s) and substituting references to Rowinp and/or Colinp with values from the margin(s).

There are three mutually exclusive possibilities, corresponding to Rowinp blank or not.

1. TABLE(Rowinp, ) Colinp blank. The formula is that in the left margin of the same row with instances of Rowinp replaced by values from the upper margin of the same column.
2. TABLE( , Colinp) Rowinp blank. The formula is that in the top margin of the same column with instances of Colinp replaced by values from the the left margin of the same row.
3. TABLE(Rowinp, Colinp) Neither blank. The formula is that in the cell at the intersection of the left and top margins with instances of Rowinp replaced by values from the upper margin of the same column and instances of Colinp replaced by values from the the left margin of the same row.

I think that should let you work out what the effective formula is in each cell of the formula range.

But I wouldn't be surprised to learn that any of the above is wrong :-0
I welcome pointers to anything more authoritative.