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 parentheses & 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 2009 and 2011, and payee is not MTA or Fandango, and category is food, shopping, or entertainment, then show "Awesome Transaction." If transaction is 2012 or later and subcategory is not Alcohol, then show "Late Transaction." If year of transaction is 2008, then show "Early Transaction". For everything else, show "CRAP Transaction"



-----------
Step 1 - Write formula in plain English

IF Transaction Year is between 2009 and 2011
     AND
     Payee is not MTA or Fandango
     AND
     Category is food, shopping, or entertainment
     THEN
     "Awesome Transaction"
     
     IF Transaction Year is 2012 or later
          AND
          Subcategory is not alcohol
          THEN
          "Late Transaction"
          
          IF Transaction Year is 2008
               THEN
               "Early Transaction"
               
               ELSE
               
               "CRAP Transaction"



-----------
Step 2 - Reposition Arguments

IF(
     AND(
          Transaction Year is between 2009 and 2011,
          Payee is not MTA or Fandango,
          Category is food, shopping, or entertainment
     ),
     
     "Awesome Transaction"
     
     IF(
          AND(
               Transaction Year is 2012 or later,
               Subcategory is not alcohol
          ),
          
          "Late Transaction"
          
          IF(
               Transaction Year is 2008,
               
               "Early Transaction"
               
               "CRAP Transaction"



-----------
Step 3 - Add Parenthases and Commas

IF(
     AND(
          [Transaction Year] is between 2009 and 2011,
          OR([Payee] is not "MTA", [Payee] is not "Fandango"),
          OR([Category] is "Food", [Category] is "Shopping", [Category] is "Entertainment")
     ),
     
     "Awesome Transaction",
     
     IF(
          AND(
               [Transaction Year] is 2012 or later,
               [Subcategory] is not "Alcohol"
          ),
          
          "Late Transaction",
          
          IF(
               [Transaction Year] is 2008,
               
               "Early Transaction",
               
               "CRAP Transaction"
          )
     )
)




-----------
Step 4 - Substitute English with Excel Syntax

IF(
     AND(
          AND( YEAR([Transaction Date]) >= 2009, YEAR([Transaction Date]) <= 2011),
          OR([Payee] <> "MTA", [Payee] <> "Fandango"),
          OR([Category] = "Food", [Category] = "Shopping", [Category] = "Entertainment")
     ),
     
     "Awesome Transaction",
     
     IF(
          AND(
               YEAR([Transaction Date]) >= 2012,
               [Subcategory] <> "Alcohol"
          ),
          
          "Late Transaction",
          
          IF(
               YEAR([Transaction Date]) = 2008,
               
               "Early Transaction",
               
               "CRAP Transaction"
          )
     )
)



-----------
Step 4B - Including Cell References

LEGEND
[Transaction Date] --> C2
[Payee] --> W2
[Category] --> T2
[Subcategory] --> U2


IF(
     AND(
          AND( YEAR(C2) >= 2009, YEAR(C2) <= 2011),
          OR(W2 <> "MTA", W2 <> "Fandango"),
          OR(T2 = "Food", T2 = "Shopping", T2 = "Entertainment")
     ),
     
     "Awesome Transaction",
     
     IF(
          AND(
               YEAR(C2) >= 2012,
               U2 <> "Alcohol"
          ),
          
          "Late Transaction",
          
          IF(
               YEAR(C2) = 2008,
               
               "Early Transaction",
               
               "CRAP Transaction"
          )
     )
)




-----------
Step 5 - Paste after removing white space

=IF(AND(AND(YEAR(C2)>=2009,YEAR(C2)<=2011),OR(W2<>"MTA",W2<>"Fandango"),OR(T2="Food",T2="Shopping",T2="Entertainment")),"Awesome Transaction",IF(AND(YEAR(C2)>=2012,U2<>"Alcohol"),"Late Transaction",IF(YEAR(C2)=2008,"Early Transaction","CRAP Transaction")))