# Convert EXCEL TABLE reference into VB.NET calculation

I am converting an EXCEL spreadsheet with over 1,000 calculations into an application.  Using VB.NET.  I have figured out all the formulas and am almost done.  I have come across a formula that I do not know what to do with.

Cell C442 contains this formula:
{=TABLE(,C241)}

The value displayed in Cell C442 is (\$666,318.32)

Cell C241 contains the value 110

Cell B442 contains the value \$100.00

Cell C440 contains the value \$248,287.49; the cell contains a reference to cell \$C\$390

I just need to know how to convert the formula in Cell B442 into a VB.Net calculation.  The definitions I have read on TABLE have not provided a good answer.

###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
The squirly brakets {=TABLE(,C241)} mean that this is an array formula, and the TABLE function comes from either an addin or is a user defined function. You will need to find which one it is and get its parameters.
Author Commented:
Thank you for the information.  I do not see any meaningul UDFs in this spreadsheet.  There are 8 macros (macro1 - marcro8) that print out certain ranges.  That means there must have been an ADDIN that I don't have.
Commented:
Check if there are any references set up. Also check the object browser and Function help on the Formula bar.
Commented:
What you have is a one variable data table. Check the cells above C442 until you come to one that does not contain a {TABLE} formula and that is the formula you are actually interested in.
Commented:
Note: this is a built-in Excel function, not an add-in or UDF.
Author Commented:
C441, directly above C442, has the formula/reference =\$C\$390, with the value \$248,287.49

The value in cell C390 is \$248,287.49

C241=110
B442=\$100.00
C441=\$248287.49

C442={=TABLE(,C241)}=\$666318.32

what is this function doing to come up with the value (666,318.32)

Commented:
Is there a formula in C390 or a literal value?
Author Commented:
C390=C388-C389

C388=C362-C387

C389=IF(C388<=0,0,IF(C388<200000.01,C388*C262,(200000*C262)+(C263*(C388-200000))))

C362=SUM(C356:C361)

C387=C378+C386

there are formulas in each of these cells
Commented:
It would be much easier to see the workbook as you have a lot of dependencies in there.

Essentially the TABLE formula substitutes the value in the cell to the left of it for the value in cell C241 in the formula that is driving it, which means that cell C241 is referred to by a formula somewhere on your sheet (typically this would be in the cell above your TABLE array, or for a 2 input table it would be the cell in the top left corner of the range).

So assuming you have the TABLE array formula in say cells C442:C450, the result in each cell will be the result of substituting the value in C241 with the value in B442:B450 in the relevant formula.

You can lookup data tables in Excel's help for examples that may make it clearer if you don't want to (or can't) post the workbook.

Experts Exchange Solution brought to you by