Returning Data from Excel Table, Mathcad 15.0

[Heavenly]

I have a table in excel and based on three input variables I need to return 1 output variable. Is it possible to just click on a cell and have that value become a variable to be used in subsequent calculations? I have attached the excel table below. What I need to do is return a value of Cf based on the grade, the width "d", and the thickness "b". For example if grade=structural, d=12, and b =3, then Cf would be 1.0 and if Grade = Stud and d=10 and b=4, then Cf would be 1.2. I need to have mathcad return the value of Cf to be used in later calulations. Thanks for any help.

[Werner_E]

Wouldn't it be easier to hardcode that small table in Mathcad or is it mandatory to read the data from the excel sheet?

Anyway it would mean writing a Mathcad routine which provides the correct Cf, which would be very specific to the excel table provided. Texts like "2&3,4" or "14&wider" doesn't make an automatic interpretation easy.

In which way you would provide b and d and especially Grade?

What should happen if invalid values are given (d=7, or Grade=Utility and d=6)? Interpolation, error message?

Is it on purpose that for Grade=Utility width d is sorted in reverse order and that here Cf values are smaller for smaller d?

A good idea in my opinion would be the use of three listboxes to select the inputs from. maybe a look at Tom Gutman's AISC steel sheet (the data here is read from an Excel sheet, too) gives some inspiration.

http://www.imakenews.com/ptcexpress/e_article001573955.cfm

[Heavenly]

What do you mean by hard coding into Mathcad table? It is not neccessary to do it through excel.

I would use a list/combo box for the grade and hard input for b and d.

The table is set for actual lumber sizes so the b and d will always match. This is a table from a design code similar to the AISC manual.

Yes that is the purpose and the values are correct.

I will look at Gutmans example thanks.

[Werner_E]

By "hard coding" I meant that the few values could be typed in a matrix in Mathcad so the file would be self contained. Using the Excel sheet makes sense if the table is large and/or if the table is to be changed from time to time.

See attached a way to do it with list boxes. I used a component from Richards collection http://communities.ptc.com/docs/DOC-1071

You will have to enable scripted components by clicking "No" at the message which pops up when the sheet is loaded.

[Werner_E · Accepted solution]

Here's a version without scripted components and numeric values for b and d.

Regards

[Heavenly]

Thanks Werner.

[Werner_E]

You are welcome.

But there is an error in the last sheet - I forgot a transpose operator. Here is the corrected version.

Also find there a function get_Cf() which might be handy.

[Heavenly]

I figured out how to do what I wanted without as much coding in Mathcad. I did what I needed in excel using the index and match functions then inserted the excel table table as a component and set the inputs and outputs. This worked great for the variable CF at the end of the sheet but when I replicated the same procedure for Fb it tells me the "range value specified is invalid". The excel part is working as I need it to but Mathcad doesn't like something. I cant figure out what's wrong because everything is done just like the variable CF. Any ideas? Thanks.

[Werner_E]

I am not getting an error. Fb calculates as does Cf. Ever so often the value is a NaN, meaning that the EXCEL formula failed and produced a #NV in cell E2.

I attach a pdf.

Maybe the error only shows for a specific combination of input values selected?

[Heavenly]

That is very strange, mine still wont work. I wonder if some setting I have in Mathcad. The NaN is because in the code some of the "grades" are not associated with some of the "species" so if you pick a grade species combination that doesn't exist it should return that value. Thanks.

[Werner_E]

David DePolo wrote:

That is very strange, mine still wont work.

Strange, indeed. I am using Mathcad 15 M020 (the latest release) - don't know if this can have anything to do with that effect.

Maybe you can post a screen shot of the error message. Is there any additional information if you use (right click) "trace error"?

[Heavenly]

Trace error doesnt provide any additional info. Attached is a screen shot.

[Werner_E]

So it looks like Mathcad has troubles acessing the cell G2 (or H2 or E2 ??) Can't imagine any reason for this, as no matter how the xl sheet is setup - those cells exist.

As I understand it the EXCEL sheet should be embedded in Mathcad and be independent of the original sheet. So even if you had changed the original sheet it should not bother your MC sheet (and even if, the cells you reference are existent in any way). So I see no reason for the error. I remember that Mathcad 14 had that kind of error sometimes if you used an xlsx file and the solution was to save it as xls first. But as it works with Cf for you I don't think that this applies. And then - I am using Office 2007 myself here and both components work.

Maybe it helps if for the output variable you specify a range in the form E2:E2 rather than simply E2. Just poking around.

[VladimirN]

I have no errors with this worksheet. I use Mathtsad 15 M020 and MS Office 2010.

[Heavenly]

I spoke with PTC technical support and they ran it with M020 and didnt get any errors either. Then he ran it with the version I am using and reproduced the same errors. The programmers informed him that this was a bug that was fixed in M010. So I am in the process of downloading M020 . Thanks for the responses.

[Werner_E]

Wouldn't have thought of that.

Probably its this one (from readme.pdf):

"2007304 Fixes a problem with assigning data to a variable using an Excel component and an existing Excel 2007 file"