Geek Answers Handbook

Extract formula from Excel data table (what-if analysis)

I came across a rewrite of an Excel project in R. I see a table that shows cell {= TABLE (F2, C2)} . I understand how to create a table like this (What-If Analysis, Data Table ...).

How should I understand this to rewrite in R, how can I find the original formula that is behind this cell?

EXAMPLE: I created a data table as shown here , and the sheet looks like this :

SO32606771 question example

In my case, I do not know how the sheet was created, and I want to know the original formula. Now this is shown as {=TABLE(,C4)} . (In the example, I know the answer, it is in cell (D10), but where is the link for this cell in the data table?)

+5
source share
2 answers

I use Excel 2007, but I have no reason to believe that they are different in other versions.

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

  • There is no help in the "TABLE" function in the local help, it is not listed in the "List of sheet functions (in alphabetical order)".
  • You cannot manually enter or edit the TABLE function; Error "This function is not valid."
  • Copying / pasting cells containing the TABLE function pastes their values, not their formulas, even if you choose Paste Special> Formulas
  • You cannot insert rows / columns directly above / to the left of cells containing the TABLE function; error "Unable to modify part of the data table."
  • Pace @pnuts using formulas> Auditing formula elements containing the TABLE function do not show use cases, and no cells show them as dependent. Although in the VBA worksheet auditing tool in which I use the Range.DirectDependents property, I found a "range of formulas" that depends on "margin" cells containing formulas, but not those that correspond to the values ​​(see below for an explanation of these terms).

I was unable to find anything that I consider worthy of the TABLE () documentation. I have found many illustrations on how to create and use this function, but nothing explicitly indicates the arguments and the result. The best I have found is https://support.office.com/en-us/article/Calculate-multiple-results-by-using-a-data-table-e95e2487-6ca6-4413-ad12-77542a5ea50b . I would be happy if anyone could point me to the best documentation.

I deduced bahavius ​​as described here:

TABLE (Rowinp, Colinp) is an array formula in an adjacent cell array. I will refer to this continuous array as the "range of formulas" of the data table. Cells located directly above / to the left of the range of formulas are also part of the data table, even if they do not contain the TABLE () function and can be edited; I will refer to these cells as "fields" of the data table.

Rowinp and Colinp must be empty or refer to separate cells. Rowinp and Colinp must be different (or the "Input cell reference is invalid" error), they must not be empty.

Values ​​in the range of formulas are calculated by accepting the formula (s) from the field (s) and replacing references to Rowinp and / or Colinp with values ​​from the field (s).

There are three mutually exclusive options that match the Rowinp space or not.

  • TABLE (Rowinp,) Colinp blank. The formula is such that the values ​​from the top field of the same column are replaced in the left field of the same row with Rowinp instances.
  • TABLE (, Colinp) Rowinp blank. The formula is such that in the upper field of the same column with Colinp instances the values ​​from the left field of the same row are replaced.
  • TABLE (Rowinp, Colinp) Not empty. The formula is that in the cell at the intersection of the left and top fields with the Rowinp instances, the values ​​from the top field of the same column are replaced and the Colinp instances are replaced by the values ​​from the left field of the same row.

I think this should allow you to determine what effective formula is in each cell of the range of formulas.

But I will not be surprised to know that any of the above is incorrect: -0
I welcome indications of something more authoritative.

+2
source

I think that in your example, F2 and C2 are actually only parameter addresses for the function ( TABLE ), where it can be located anywhere, with the corresponding formula in the upper left cell of the table.

So, I suggest going to C2 , FORMULAS> Audit Auditing and clicking "Trace Dependencies", repeat for F2 and see what the arrows converge with.

+1
source

More articles:


All Articles