Programming a loop to seek specific cells from an excel file

[ChrisPLM]

Hello everyone,

I am trying to figure out if what we would like to do is ever possible.  Please let me paint the picture first.

I have a matrix in an excel file. something like this

Mat/Temperature	-20	-15	-10	-5
Mat1	Value1	Value2	Value3	Value4
Mat2	Value5	Value6	Value7	Value8
Mat3	Value9	Value10	Value11	Value12
Mat4	Value13	Value14	Value15	Value16
Mat5	Value17	Value18	Value19	Value20

The table above is just for illustration, Our table is very large, we call it our Material Database.

The idea is as follow,

When an engineer needs to perform a certain type of calculation, he or she will open a MathCAD template and select the appropriate material and temperature at which he or she wants to perform a certain type of calculation.

MathCAD should then identify the correct cell in excel and return into MathCAD the correct value.

The idea is to have two loops.  One to identify which raw.  eg if I select Mat3,  the program will check in Column A and start with the first cell.  Does A2 = Mat3 ? No, check next line,  Does A3=Mat3? No etc... until there is a match.  Here it will be A4. It means that we are here interested in row 4.

The second loop is to identify the correct column. If the user selected Temperature -10, following the same logic as above.  Does B1=-10? No, check next column,  Does C1=-10 ? No etc... until there is a match.  Here the match will be D1.

Now the row and column have been identified  row 4  and column D.  Therefore the value that is required for the MathCAD calculation is in the cell D4  ie Value11

Is that even possible to do in MathCAD ?

I know how to get a value from excel for instance using READEXCEL("..\..\excelfile.xlsx","Worksheet1!D4")

Our challenge for now is to be able to have D4 as a variable and to get this variable calculated using the above described loops.

I was just wondering if that is even possible before we spent days of trying to figure out.

Thanks

Best regards

[Fred_Kohlhepp]

Yes, this  is possible; a search of this forum may locate a Mathcad sheet that shortens your development time.

Mathcad is quite adept at arrays.  The Mathcad function "match" will allow you to locate positions in an array.

[Werner_E]

Yes, its sure possible and its far easier to implement than you think as you don't need to program loops yourself. A clever combination of one of the various lookup function and the match function will do the job quite easily.

Furthermore you could as easy interpolate between the values so that also a temperature of. lets say, -17 could be chosen - if that makes sense in your application.

[nas0k]

Maybe something like this?

I hope this helps.

[ChrisPLM]

Thanks a lot Guys,

We'll have a go and reverse back.

[Werner_E · Accepted solution]

Not sure if you intend to use Mathcad 15 or Prime and i don't what exact structure you have in mind to represent your data in Mathcad (row and column header as vectors and raw data in a matrix or a full table with row and column headers included).

So here are a few ways to do what you want (no error checking implemented:

[Werner_E]

Here is a way to use interpolation. Its easiest with separate row and column headers but could be adapted for other structures as well.

Here a simple linear interpolation is used - could be easily changed for a spline interpolation.

[jsuarez]

Hi Werner,

Your are the undisputable KING of Mathcad programmer of the whole Mathcad community forum.

juan

[Werner_E]

Sure not! but if you do something for quite some time, you learn more and more about it.

If you are for some really impressive stuff you may search the forum for some other contributors work, e.g. Stuart's.

[ChrisPLM]

Hello Werner

I like your modesty   You have provided me and the community great information here.

To answer your questions.

1) We are using Prime 4.0

2) We do not know yet exactly what structure to use. We do have one as we have something working in excel only but since we bought MathCAD we are recreating the calcs from Excel into MathCAD. One of our issue was a very large table of material properties I would like to avoid recreating (not so much for the work involved but it took ages to approve and validate it). The table is a bit more complex than explained as for a given material and temperature, it returns 3 values (yeld stress, Ultimate Tensile Strength and Young's Module)

We will have some play around with your suggestions (as well as those from others) and see what suit us best.

Thank you very much all for your help. This is much appreciated.