Skip to main content
C
CB_102797024-Participant
4-Participant
May 10, 2023
Question

Using Conditional Formatting in Matrices

  • May 10, 2023
  • 1 reply
  • 1483 views

Hi,

 

I'm attempting to use conditional formatting to determine whether values meet certain criteria within a matrix, and then based on those results, manipulate the values within the matrix by certain formulas embedded within the if/then statements. 

 

Right now I have the contents of the original matrix (output from the embedded excel file), and I'm attempting to use a separate variable (Mcalc) to use step functions to move in both directions within the matrix to identify each value within the matrix to confirm whether it meets certain criteria. I believe I'm getting hung up on the portion where I'm trying to use two separate step functions within the definition of a single variable.

CB_10279702_0-1683755656335.png

 

My other question relates to the embedded excel I have within the document. Is there any way to perform the calculation I have within the embedded excel (a simple formula that references two separate vectors of different lengths) outside of excel?

CB_10279702_1-1683755690463.png

 

Thanks in advance,

 

 

1 reply

W
Werner_E25-Diamond I
25-Diamond I
May 10, 2023

Its not clear to me what you are trying to achieve.

Your matrix M is 49 x 14, but your program seems to only deal with the top 7 rows.

Furthermore I don't understand why you use two if-statements, the only difference being the 1 ksi or 0 ksi in the else branch.

From your verbal description it looked to me that you simply want to look through all elements in the matrix and depending on its value replace it by some other value.

Here I use your first if-statement and apply it to every element in the matrix

Werner_E_0-1683757265904.png

In case you only want to treat the first N.As rows, you could do it as follows

Werner_E_1-1683757669007.png

But the rows after N.As contain only zeros anyway

Of course you could use a program with nested for loops, too

Werner_E_2-1683758068279.png

 

 

 

    C
    CB_102797024-ParticipantAuthor
    4-Participant
    May 11, 2023

    Thank you so much for a quick and detailed reply. Let me provide a bit more context about my objective here. 

     

    My goal is to replicate an existing vba script and perform the analysis exclusively in Mathcad. Here is a snippet of the VBA that we're trying to replicate here.

     

    CB_10279702_2-1683840150508.png

     

    You can see that most of the calculations/definitions at the top of the snippet are replicated within the Mathcad worksheet, including the calculation for B1 and the definition of the other miscellaneous variables down to where the step function begins. Where I'm struggling involves replicating this data in a matrix. 

     

    CB_10279702_1-1683839198486.png

     

    Rather than model this data in an embedded excel sheet within the Mathcad worksheet as I currently have (snippet above), the goal is to model this information in a matrix or table that dynamically incorporates the data from the inputs above within the worksheet. As you noted above, the table by default has 49 rows, but my goal is to model this in a table with the number of rows corresponding to the "NAs" variable defined in the worksheet. 

     

    As you can see in the snippet, column 1 references the values of the array "d", the number of which references the value of "NAs". Row 1 references values from the transposed array "c". Referencing the VBA script in the first snippet, the matrix/table would then perform a calculation for each individual value in the table by the formula "es=0.003*(1-d/c)". 

     

    Is this possible to do using a matrix?

     

    Then, depending on that value, I could use the conditional function I listed below that in the worksheet, and solve for fs (this is easy, and I believe I've already done that). 

     

    Finally, the last conditional statement I'm also struggling to incorporate is the determination as to whether "d" is greater than "a". Remember, "a" is simply a function of "c", the values of which could also be listed in the top row of the table, Do you know of a way to embed this conditional statement to make the determination if "d" is greater than "a" and apply arithmetic to the values within the original table per the VBA script?

     

    Thanks for your help, 

    Terms & ConditionsAccessibility statement