How to use "conditional statements" to select data rows to then execute e.g. simple Col A - Col C

[#1]

Need some help!

I want to select data rows from 3 or 4 columns (based on CONDITIONAL statements, then execute rather simply formula e.g. if conditions met in selecting the correct rows

If(
:YEAR == 2020, :CROP | Corn | Soybean,
:TYPE == IF, :"Mean(YIELD(BU/A)_OR, 13)"n - :"Mean(YIELD(BU/A)_OR, 8)"n,
:ELSE == :EMPTY (I prefer to not use ELSE statement if it is possible....just want the above conditional statements to be met to know exactly what gets added in new column via formula)
OR,

If(
:YEAR == 2020, :CROP | Corn | Soybean,
:TYPE == FOLIAR, :"Mean(YIELD(BU/A)_OR, 4)"n - :"Mean(YIELD(BU/A)_OR, 1)"n,

OR,

If(
:YEAR == 2021, :CROP | Corn | Soybean,
:TYPE == IF, :"Mean(YIELD(BU/A)_OR, 6)"n - :"Mean(YIELD(BU/A)_OR, 2)"n

OR,

If(
:YEAR == 2021, :CROP | Corn | Soybean,
:TYPE == FOLIAR, :"Mean(YIELD(BU/A)_OR, 3)"n - :"Mean(YIELD(BU/A)_OR, 1)"n
)

[#2]

I am having trouble interpreting your pseudo code. 

I assume you have a numeric column called YEAR and that you are checking to see if it has the value 2020

Do you have a column  named:

     CROP | Corn | Soybean

or are you wanting a column named CROP to have a value of Corn or Soybean

It appears you have a column named TYPE and you are checking to see if it has the value of "IF"?

and if the value is equal to "IF" then you want to find the difference between a column named Mean(YIELD(BU/A)_OR, 13) and a column named Mean(YIELD(BU/A)_OR,   

or do you want to calculate the mean for column YIELD

and finally you are referencing a column named ELSE and determining if is equal to a column named EMPTY.

I am sure this is not what you want.

Can you provide a sample of what the data table is and then describe in words, what you want.

I also suggest that you take the time to read the Scripting Guide.  It is available in the JMP Documentation Library, under the Help pull down menu.

[#3]

Hi Jim, thanks for replying and I appreciate any help you can offer and sorry for the confusion - I'll try to clarify and simplify the type of formula I am wanting.

I have a column named YEAR and values are either 2020 or 2021.

I have a another column named CROP and values are either Corn or Soybean.

I have another column named TYPE and values are either Foliar or IF (i.e., in-furrow)

I have another 10 columns that represent the Mean Yield of Treatments 1 through 10 (TRT1, TRT2,......,TRT10).

I want a formula to calculate the added value of each Treatment (different product dose rates) vs. the Control Treatment (0 rate).  Here's the kicker, the Treatment numbers (representing dose rates 0, 1, 4, 8, 16, 32, 64 FL OZ/acre) differed between the 2 years and between CROP and TYPE.  For example, 16 FL OZ/acre product dose rate was TRT5 in 2020 for both Corn and Soybean "IF" i.e., represents in-furrow trials, and was TRT3 in both Corn and Soybean "Foliar" trials in 2021.

So I have 8 possible combinations of YEAR (2) x CROP (2) x TYPE (2) and I'd like 1 formula to fill in the values for change in bu./acre for a given product dose rate vs. the Control Treatment (0 rate)....so for example, I create a new column named "16FLOZ_AddedValue" and want the formula to do:

If YEAR = 2020, CROP=Corn, TYPE=Foliar, the formula needs to return value of "TRT3" minus column "TRT1"; however,

If YEAR = 2020, CROP=Corn, TYPE=IF (i.e. in-furrow), the formula needs to return value of "TRT5" minus column "TRT1" in same column named "16FLOZ_AddedValue"; however,

If YEAR = 2021, CROP=Corn, TYPE=Foliar, the formula needs to return value of "TRT4" minus column "TRT1"" in same column named "16FLOZ_AddedValue"; 

etc. (sorry, I do not know how to string the "IF"s together in 1 formula).

Regarding the ELSE comment, that just pertains to my desire to not have the formula do any sort of hidden action steps when the "IF" combination is not met.  In other words, for example, if YEAR=2020, CROP=Corn, TYPE=Foliar, then only calculate TRT3-TRT1 and omit any ELSE type of action, i.e. leave all remaining cell values untouched; and move to the next combo IF statements that might direct the formula to take TRT5-TRT1, etc.

[#4 · Accepted solution]

BIG THANK YOU TO: Sarah Seligman, SAS Technical Support for providing a solution to my question.  The cut/paste from her email is below but formatting is lost so I have attached her X.jmp data file and a pdf of her answer where it might be easier to see the JSL format in the formula.  Again, my thanks to Sarah and to Jim for initially responding to my JSL question.

___

Hi Jeff, Thank you for your patience. I've attached a data table containing a formula that does what I believe you described. Regarding the part of your question where you asked "Is it possible to structure IF statements in a way that omits ELSE action?" The simple answer to that is yes - you just leave the "Else" piece of the conditional IF statement blank. So, with one IF statement, it would look something like this in the Formula Editor: In JSL, that is: If(:YEAR == "2020" & :CROP == "Corn" & :TYPE == "Foliar", :TRT 03 - :TRT 01 ) With two IF statements, it would just look like this: In JSL, that is: If( :YEAR == "2020" & :CROP == "Corn" & :TYPE == "Foliar", :TRT 03 - :TRT 01, :YEAR == "2020" & :CROP == "Corn" & :TYPE == "IF", :TRT 05 - :TRT 01 ) If you are using the Formula Editor rather than JSL, you can insert a new IF condition by clicking on the caret icon. In the screen capture below, I clicked it three times to add three more If expression fields. Getting back to the goal you described: I create a new column named "16FLOZ_AddedValue" and want the formula to do: If YEAR = 2020, CROP=Corn, TYPE=Foliar, the formula needs to return value of "TRT3" minus column "TRT1"; however, If YEAR = 2020, CROP=Corn, TYPE=IF (i.e. in-furrow), the formula needs to return value of "TRT5" minus column "TRT1" in same column named "16FLOZ_AddedValue"; however, If YEAR = 2021, CROP=Corn, TYPE=Foliar, the formula needs to return value of "TRT4" minus column "TRT1"" in same column named "16FLOZ_AddedValue"; Below is the formula for new column 16FLOZ_AddedValue based on that description. In the attached data table, you can click on the blue plus icon next to the column name in the columns panel to see it in the formula editor. In JSL, this formula is written as: If( :YEAR == "2020" & :CROP == "Corn" & :TYPE == "Foliar", :TRT 03 - :TRT 01, :YEAR == "2020" & :CROP == "Corn" & :TYPE == "IF", :TRT 05 - :TRT 01, :YEAR == "2021" & :CROP == "Corn" & :TYPE == "Foliar", :TRT 04 - :TRT 01 ) You also stated: Regarding the ELSE statements, my desire to not have the formula do any sort of hidden action steps when the "IF" combination is not met. In other words, for example, if YEAR=2020, CROP=Corn, TYPE=Foliar, then only calculate TRT3-TRT1 and omit any ELSE type of action, i.e. leave all remaining cell values untouched; and move to the next combo IF statements that might direct the formula to take TRT5-TRT1, etc. Is it possible to structure IF statements in a way that omits ELSE action? The formula I created sets the appropriate value when the criteria is met for the first IF condition, then it moves to the next IF condition, and so on. You don't have to specify any ELSE condition/value. I hope this information is helpful. Please let me know if you have any additional questions. Kind Regards, Sarah Seligman JMP Technical Support Share. Ask. Learn. Connect. - community.jmp.com I will be happy to continue working with you should you have any follow-up questions regarding this matter; simply reply to this email within 5 business days. If this response has fully answered your question, please respond to let me know that you've received this email and I have resolved this issue to your satisfaction. Attachments 7613517300.jmp (6 Kb)