Forum Discussion

Stymied9's avatar
Stymied9
Frequent Visitor
9 years ago
Solved

IF / AND / OR statements

This is the Excel version of the formula I am using which is not working:

=IF(OR(V3>19958*0.85,AND(Y3="20FT_COFC",AF3>33*0.85),AND(Y3="40FT_COFC",AF3>67*0.85),AND(Y3="40FT_HC",AF3>76*0.85)),"","Not ")&"Optimized"



 Attached/Below is an excerpt of the data set:



 Basically this is what I am trying to do:

Argument 1
If the column V (Total Weight) is greater than 85% of 19958, I want the column to indicate "Optimized".

Argument 2
If Column Y (Equipment Type) = 20FT_COFC and column AF is greater than 85% of 33, I want the column to indicate "Optimized"

Argument 3
If Column Y (Equipment Type) = 40FT_COFC and column AF is greater than 85% of 67, I want the column to indicate "Optimized"

Argument 4
If Column Y (Equipment Type) = 40FT_HC and column AF is greater than 85% of 76, I want the column to indicate "Optimized"

If none of those 4 arguments are true, then I want the column to indicate "Not Optimized"

I hope that makes sense. Thanks !

  • Anonymous's avatar
    Anonymous
    9 years ago

    Would something like this work?

    ResultColumn = IF(
    	[Total Master Wgt] > (19958 * 0.85),
    	"Optimized",
    	SWITCH(
    		[Equipment Type],
    		"20FT_COFC", IF([CBM] > (33 * 0.85), "Optimized", "Not Optimized"),
    		"40FT_COFC", IF([CBM] > (67 * 0.85), "Optimized", "Not Optimized"),
    		"40FT_HC", IF([CBM] > (76 * 0.85), "Optimized", "Not Optimized"),
    		"Not Optimized"
    	)
    )


    You'll need to check your field names, i've done this based on your screenshot.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Would something like this work?

    ResultColumn = IF(
    	[Total Master Wgt] > (19958 * 0.85),
    	"Optimized",
    	SWITCH(
    		[Equipment Type],
    		"20FT_COFC", IF([CBM] > (33 * 0.85), "Optimized", "Not Optimized"),
    		"40FT_COFC", IF([CBM] > (67 * 0.85), "Optimized", "Not Optimized"),
    		"40FT_HC", IF([CBM] > (76 * 0.85), "Optimized", "Not Optimized"),
    		"Not Optimized"
    	)
    )


    You'll need to check your field names, i've done this based on your screenshot.

     

    • Stymied9's avatar
      Stymied9
      Frequent Visitor

      That DID work Ross !  Thank you very much!