Using match function in a loop

[Raiko]

Hello folks,

I ran into this problem of the match function. When used in a loop it claims to not have found a match. When used on its own it works just fine; see attached worksheet.

Does anybody know why? I'm using MC15 on a Win 7 machine.

Thanks in advance

Raiko

[MikeArmstrong]

Please upload the Excel data.

[Raiko]

here we go Mike. It ought to work however with any Excel data.

Raiko

[MikeArmstrong]

Firstly are calling the wrong column of Q? Should you be calling column <2>?

[Raiko]

I'm calling the second column of Q by invoking the matrix column command "Q<1>". That has worked so far.

Raiko

[MikeArmstrong]

The second column is Time - I assume you are not trying to compare the range month to time.

[MikeArmstrong]

Something like the attached?

[Raiko · Accepted solution]

Tghank you Mike for the hint. It led me to the solution. I simply had to trap the error when match didn't find a solution and now it's working like a charm.

Raiko

[MikeArmstrong]

RaikoRaiko Milanovic wrote:

Tghank you Mike for the hint. It led me to the solution. I simply had to trap the error when match didn't find a solution and now it's working like a charm.

Raiko

Super news. Could you please provide the solution to help others.

[Raiko]

Both columns contain plain numbers. The second represents the months.

What I'm trying to do is to determine the averages of reliabilities per month. In this example only January and December are available, hence only the 1 and the 12 are present.

In other sheets I do have all months represented by numbers 1 through 12.

[MikeArmstrong]

RaikoRaiko Milanovic wrote:

Both columns contain plain numbers. The second represents the months.

What I'm trying to do is to determine the averages of reliabilities per month. In this example only January and December are available, hence only the 1 and the 12 are present.

In other sheets I do have all months represented by numbers 1 through 12.

I'm sorry Raiko I am a little confused. In the data you provide column 3 contains values of 1 and 12, which coincide with the months of Jan and Dec, correct?

So which columns to you need to extract.

Please provide a simple example of the expected results.

[Werner_E]

I'm sorry Raiko I am a little confused. In the data you provide column 3 contains values of 1 and 12, which coincide with the months of Jan and Dec, correct?

Raiko's Excel files consists of two sheets. You are referring to the first one "Sheet1" but Raiko is accessing the second one "FVW" which consists of two columns only.

@Raiko: Are you looking for something like this:

[MikeArmstrong]

I didn't notice the second sheet. However, the second sheet is just reproducing the last two columns of sheet 1.

[Werner_E]

Mike Armstrong wrote:

I didn't notice the second sheet. However, the second sheet is just reproducing the last two columns of sheet 1.

Yes, but it would not be necessary to use Excel itself to get the month number or make a copy of the data.

Using an appropriate Mathcad routine which converts an Excel date number to a date we could use the "raw data" itself:

[MikeArmstrong]

Nicely done.

[Raiko]

Hello Mike,

no need to worry any longer. I found a workaround to trap the error.

Sorry for the Excel sheet, I forgot to mention that I'm accessing only sheet 2.

Raiko

[MikeArmstrong]

No problem, glad you got the solution.