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.


JANWIL78Asked:
Who is Participating?
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.

leonstrykerCommented:
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.
0
JANWIL78Author 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.
0
leonstrykerCommented:
Check if there are any references set up. Also check the object browser and Function help on the Formula bar.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Rory ArchibaldCommented:
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.
0
Rory ArchibaldCommented:
Note: this is a built-in Excel function, not an add-in or UDF.
0
JANWIL78Author 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)

0
Rory ArchibaldCommented:
Is there a formula in C390 or a literal value?
0
JANWIL78Author 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
0
Rory ArchibaldCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JANWIL78Author Commented:
Thank you!  I had looked up the data tables in Excel's Help and had a hard time applying it to my situation...so thus I am reaching out.  I can't post the workbook because it is confidential.  I am going to take your explanation and apply it.  It makes sense what you are saying.  I just need to take it and apply it to the worksheet and see if I can come up with the correct values.  IF I cannot make sense of it, I do have GOTOASSIST where I could send a link and we could take a look on my PC.  I am not sure if that is allowed thought.  I will get back to you.  Thank you.
0
JANWIL78Author Commented:
I was able to apply your explanation to the spreadsheet and converted the formula to VB.NET code.   The way you explained what TABLE was doing and applied it specifically to my spreadsheet and example was the ticket..thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.