cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Table/Vector (Excel import) compare value and replace

SOLVED
Highlighted

Table/Vector (Excel import) compare value and replace

Hello,

On my worksheet, I import an Excel table with about 50.000 lines, name is "signal".

With a formula, I convert the input Excel value (Amper) to (Volt) units.

After, I would like to create a new table, with 5V when "signal" is HIGHER than a constante name's (SeuilHIGH) and 0V if is lower.

I try a boucle for, match, but I don't manage to my objective.

With match(SeuilHIGH, signal, "gt"), it display the index of the table, but how can I replace the value by the good one?

I attached the file.

Excel importation is on page 2 and computation on page 2 and 3.

have you got an idea in order to create a new table/matrix with only two values according to the first table "signal"?

Thank you,

have a good week,

Emilien

Worksheet is for mathcad 2.0 (and 3.0 I hope)

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Table/Vector (Excel import) compare value and replace

There are quite some different errors in your attempts to create that vector OUT so I won't comment on every single one. So just 2 points:

1) It looks to me that you are unsure about local and global variables. As a generla rule you can not change worksheet variables from within a program block. The only way a program can change a worksheet variable is when you explicitely assign that variable to the output of the program (WSvariable:=program..). So whatever is to the left of a local assignment (<---) is always a variable local to the program, no matter if its named the same as a worksheet variable.

2) A program either returns the very last value which is evalauted or whatever you chose to return using the statement "return". So if your program ends with a for-loop which assigns values to a vector X, only the last assigned single value of that vector would be the result of the whole program. If you want the full vector as result you have to put X (or return X) as the last line of the program.

Find attached half a dozen ways to create that vector OUT.

The shortest way sure is to write: OUT:=(signal>Seuil_HIGH)*5V

More generic: OUT:=(signal>threshold)*(Upper - Lower) + Lower (values lower than threshold are set to Lower, the rest to Upper).

At the moment i am working on a machine with no Prime 2 installed and I didn't notice at first that you probably are using Prime 2. So the file is in Prime 3 format and unfortunately there is no way to convert it to any other format, not even Prime 2. So I additionally attach a pdf of the routines I provided - think that should get you up and running.

3 REPLIES 3

Re: Table/Vector (Excel import) compare value and replace

There are quite some different errors in your attempts to create that vector OUT so I won't comment on every single one. So just 2 points:

1) It looks to me that you are unsure about local and global variables. As a generla rule you can not change worksheet variables from within a program block. The only way a program can change a worksheet variable is when you explicitely assign that variable to the output of the program (WSvariable:=program..). So whatever is to the left of a local assignment (<---) is always a variable local to the program, no matter if its named the same as a worksheet variable.

2) A program either returns the very last value which is evalauted or whatever you chose to return using the statement "return". So if your program ends with a for-loop which assigns values to a vector X, only the last assigned single value of that vector would be the result of the whole program. If you want the full vector as result you have to put X (or return X) as the last line of the program.

Find attached half a dozen ways to create that vector OUT.

The shortest way sure is to write: OUT:=(signal>Seuil_HIGH)*5V

More generic: OUT:=(signal>threshold)*(Upper - Lower) + Lower (values lower than threshold are set to Lower, the rest to Upper).

At the moment i am working on a machine with no Prime 2 installed and I didn't notice at first that you probably are using Prime 2. So the file is in Prime 3 format and unfortunately there is no way to convert it to any other format, not even Prime 2. So I additionally attach a pdf of the routines I provided - think that should get you up and running.

Re: Table/Vector (Excel import) compare value and replace

Hello Werner Exinger

Thank you very much for your PERFECT answer.

I know now, how to compare these types of value.

To my mind, the most explicit is this one :

mathcadOUT5.JPG

have a good day,

Emilien

Re: Table/Vector (Excel import) compare value and replace

You have to find a balance between good readability and compactness. While the shortness of the first way with the boolean expression is facinating, I also would not vote for it as its much harder to read and understand.

If a sheets need to do this substitution of values more than once I'd chose the last functional approach, otherwise the one you have chosen will do as well. Anyway you have the choice ;-)