Can't find a formula when a CellFormula is of type "dataTable"

[magedrick]

I'm writing an application in C# to read formulas from various cells in an Excel spreadsheet, and one section is giving me trouble. In Excel, it displays the formula as "{=TABLE(,B5)}" for all cells in this range.

I stepped through my C# application to see what the properties are, but there's no actual formula listed anywhere I can find (the CellFormula is an empty string). The InnerXml/OuterXml indicates a CellFormula, I tried reading through the SDK documentation for them, but it is confusing and vague when it comes to "dataTable" types.

Supposedly the relevant information is in the top-left corner of the dataTable section, which is cell D15. This is how the OuterXml looks:

<x:c r="D15" s="377" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><x:f t="dataTable" ref="D15:G35" dt2D="0" dtr="0" r1="B5" ca="1" /><x:v>3.0000000000000001</x:v></x:c>

Where is the formula? How does Excel know what to calculate in this range D15:G35?

[magedrick]

Cool, thanks for nothing.

[mikeebowen]

Hi @magedrick ,

Apologies for the delayed response. I will research this and let you know what I find out.

[mikeebowen]

Hi @magedrick ,

dataTable formulas do not work the way that normal formulas do. With a normal formula you have e.g. =C2*C6+10, which tells Excel to multiply cell C2 by the value of C6 and then add 10.

From ISO/IEC 29500 18.3.1.40 f (Formula)
"A data table is a range of cells that shows how changing certain values in one or more formulas affects the results of those formulas."

Meaning that the dataTable formula is not a normal formula itself, but rather displays the value of a normal formula in a different cell, but with alternative inputs. So in your xml you have t="dataTable", which tells Excel it is a dataTable formula. Then ref="D15:G35" indicates the first row of cells in which to display alternate values given different inputs. dt2D="0" indicates that the dataTable formula is 1 dimensional. dtr="0" indicates that this is a column and not a row. r1="B5" indicates the first input cell for the data table (the value being replaced) is in cell B5. ca="1" means the formula needs to be recalculated the next time calculation is performed, but the ca property is unrelated to data tables.

The dataTable isn't a formula itself, it shows what other formulas would display given alternative inputs. In your example, dt2D="0" means it's a 1 dimensional array, dtr="0" indicates it's a column dataTable and ref="D15:G35" are the cells that will display the alternative solutions given different variables. The dataTable must be in a table and this is a 1 dimensional column table, so the row headers (C15:C35) have the replacement values for cell B5 and the column headers (D14:G14) have the value of the original cell to calculate against.

The values in the dataTable are pulled from the upper-left of a 2d and directly above/left of a 1d range. Note that in the file the range specified is only where the values go, not including the inputs.

• The below image has borders on the ref specified in the file, and the yellow cell is the corresponding value we fill the table with
• While it's not a perfect match to the extent that a data table has a "formula" it's likely found in that yellow cell.