Skip to main content
P
ptc-54526701-Visitor
1-Visitor
June 27, 2014
Question

Reading an Excel file to an array and then programming issues

  • June 27, 2014
  • 7 replies
  • 3582 views

Hey guys,

I've read two columns of an excel file to a mathcad file that looks something like:

0
1

1

a
2b
3c
4d

I have a series of equations set up in a program, and i have a while loop set up as:

{{While (b[a,0 < f(x))

{{a=a+1

{{b[a,1

Where b represents the array made from the excel file and a's initial value is zero. I want it to read the value of column zero and if the data from the function doesn't match the value in the current row, I want the program to loop and read the next row. When the value from the equation matches the value from the array, I want the program to return the corresponding value from the same row in column 1.

It keeps returning an error message saying "This array index is invalid. The index must be an integer, not less than the ORIGIN, and not greater than the last element." And there is a red box around the a,0 in b[a,0.

Any help or advice here would be greatly appreciated!

7 replies

M
MikeArmstrong1-Visitor
1-Visitor
June 27, 2014

Please upload the worksheet.

P
ptc-54526701-VisitorAuthor
1-Visitor
June 27, 2014

I can't really upload the excel file to the internet. It has sensitive material that I'm not at liberty to share with people

M
MikeArmstrong1-Visitor
1-Visitor
July 2, 2014

You could always replicate the problem and post a dummy worksheet.

F
Fred_Kohlhepp23-Emerald I
23-Emerald I
June 27, 2014

Something like this?

P
ptc-54526701-VisitorAuthor
1-Visitor
June 27, 2014

I'm not 100% sure as I'm using prime 2.0. I'm not really familiar with this flavor of mathcad. However if your Program(1,excel)="a" are similar to Werner's Lookup executions then yes that worked. I dont completely understand the programming section written above it though it does look similar to mine.

Werner_E
Werner_E25-Diamond I
25-Diamond I
June 27, 2014

Your explanation is different from the program fragment you show.

Obviously you assume that the first column is in ascending order. When should be triggered: If the value of b[a,0 >= f(x) as your program says or if b[a,0 = f(x) as your text says? What should happen if no value in the first column is equal to f(x)? What if all values are smaller? Error message, NaN, ...?

Ferd showed one way following the code you provided. Another way would be to use vlookup() or Vlookup(). Which one of the two would be appropriate depends on the answers to the questions above.

Its hard to tell what throws the error you described, you would have to attach your worksheet. Best guess is that f(x) is greater than all values in your first column and so a will be incremented until its greater than the number of the last row - then the error is thrown. To avoid that Fred has inserted that a<N constraint.

27.06.png

P
ptc-54526701-VisitorAuthor
1-Visitor
June 27, 2014

I used these lookup functions and they returned an answer that I need. Thanks very much!!!

Terms & ConditionsAccessibility statement