The Shir Substitution Method (WRASP)

1. Write out entire formula in plain English and indent with tabs for easy viewing
2. Reposition arguments until they are in the correct order
3. Add parenthases & commas to ensure proper order of operations (PEMDAS) and argument separation
4. Substitute English with proper Excel syntax
5. Paste into formula bar after removing extra white space (find & replace)


NESTED IF CHALLENGE QUESTION

If year of transaction is between 2007 and 2009, and payee is not MTA or Fandango, and category is food, shopping, or entertainment, then show "Awesome Transaction." If transaction is 2010 or later and subcategory is not Alcohol, then show "Late Transaction." If year of transaction is 2006, then show "Early Transaction". For everything else, show "CRAP Transaction"


------------
Step 1

IF
     [Year] is between 2007 and 2009
     and
     [Payee] is NOT "MTA" or "Fandango"
     and
     [Category] is "Food", "Shopping", or "Entertainment"
     
     Then
     
     "Awesome Transaction"
     
     IF
          [Year] is 2010 or later
          and
          [Subcategory] is NOT "Alcohol"
          
          Then
          
          "Late Transaction"
          
          IF
               [Year] is 2006
               
               Then
               
               "Early Transaction"
               
               "CRAP Transaction"



------------
Step 2

IF
     AND(
          AND([Year] is greater than equal to 2007,
          [Year] is less than or equal to 2009
          )
          OR(
          [Payee] is NOT equal to "MTA",
          [Payee] is NOT equal to "Fandango"
          ),
          OR(
          [Category] is "Food",
          [Category] is "Shopping",
          [Category] is "Entertainment"
          )
     )
     
     Then
     
     "Awesome Transaction"
     
     IF
          AND(
          [Year] is greater than or equal to 2010,
          [Subcategory] is NOT equal to "Alcohol"
          )
          
          Then
          
          "Late Transaction"
          
          IF
               [Year] is equal to 2006
               
               Then
               
               "Early Transaction"
               
               "CRAP Transaction"




------------
Step 3

=IF(
     AND(
          AND([Year] is greater than equal to 2007,
          [Year] is less than or equal to 2009
          ),
          OR(
          [Payee] is NOT equal to "MTA",
          [Payee] is NOT equal to "Fandango"
          ),
          OR(
          [Category] is "Food",
          [Category] is "Shopping",
          [Category] is "Entertainment"
          )
     )
     
     ,
     
     "Awesome Transaction"
     ,
     IF(
          AND(
          [Year] is greater than or equal to 2010,
          [Subcategory] is NOT equal to "Alcohol"
          )
          
          ,
          
          "Late Transaction"
          ,
          IF(
               [Year] is equal to 2006
               
               ,
               
               "Early Transaction"
               ,
               "CRAP Transaction"
          )
     )

)



------------
Step 4

=IF(
     AND(
          AND(G2 >= 2007,
          G2 <= 2009
          ),
          OR(
          W2 <> "MTA",
          W2 <> "Fandango"
          ),
          OR(
          T2 = "Food",
          T2 = "Shopping",
          T2 = "Entertainment"
          )
     )
     
     ,
     
     "Awesome Transaction"
     ,
     IF(
          AND(
          G2 <= 2010,
          U2 <> "Alcohol"
          )
          
          ,
          
          "Late Transaction"
          ,
          IF(
               G2 = 2006
               
               ,
               
               "Early Transaction"
               ,
               "CRAP Transaction"
          )
     )

)

...Legend...

[Year] --> G2
[Payee] --> W2
[Category] --> T2
[Subcategory] --> U2



------------
Step 5

=IF(AND(AND(G2>=2007,G2<=2009),OR(W2<>"MTA",W2<>"Fandango"),OR(T2="Food",T2="Shopping",T2="Entertainment")),"Awesome Transaction",IF(AND(G2<=2010,U2<>"Alcohol"),"Late Transaction",IF(G2=2006,"Early Transaction","CRAP Transaction")))