Forum Discussion
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 !
- Anonymous9 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
- AnonymousNot 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.- Stymied9Frequent Visitor
That DID work Ross ! Thank you very much!