Can't get this nested IF statement to work

Options

=IF([Cost Incurred / Hired]@row = "Remove Cost", IF(OR([Spend Bucket]@row = "Blue Badge", [Spend Bucket]@row = "Red Badge"), -1,

IF(OR([Cost Incurred / Hired]@row = "TBH", [Cost Incurred / Hired]@row = "Cost Incurred / Hired", [Cost Incurred / Hired]@row = ""),

IF(OR([Spend Bucket]@row = "Blue Badge", [Spend Bucket]@row = "red Badge"), 1, ""))))


I'm trying to say, if the cost incurred column is 'removed cost' and the spend bucket column is either 'blue badge' or 'red badge' then the value should return as "-1". But if there is any other value in the cost incurred column and the spend bucket column is either 'blue badge' or 'red badge' then the value should be "1".. if neither 'blue badge' or 'red badge' then it should be blank.


Any idea what I'm missing here?

 

Best Answer

  • mtetzlaf
    mtetzlaf ✭
    Answer ✓
    Options

    Thankyou!

    Just before you sent this I was able to figure it out with this formula:

    =IF([Cost Incurred / Hired]@row <> "Remove Cost", (IF(OR([Spend Bucket]@row = "Blue Badge", [Spend Bucket]@row = "red Badge"), 1, "")), IF([Cost Incurred / Hired]@row = "Remove Cost", IF(OR([Spend Bucket]@row = "Blue Badge", [Spend Bucket]@row = "Red Badge"), -1, "")))

    Though, yours is much simpler 🙂

Answers

  • mtetzlaf
    Options

    =IF(AND([Cost Incurred / Hired]@row <> "Remove Cost", IF(OR([Spend Bucket]@row = "Blue Badge", [Spend Bucket]@row = "red Badge"), 1, IF(AND([Cost Incurred / Hired]@row = "Remove Cost", IF(OR([Spend Bucket]@row = "Blue Badge", [Spend Bucket]@row = "Red Badge"), -1, ""))))))

    =IF([Cost Incurred / Hired]@row <> "Remove Cost", IF(OR([Spend Bucket]@row = "Blue Badge", [Spend Bucket]@row = "red Badge"), 1, IF([Cost Incurred / Hired]@row = "Remove Cost", IF(OR([Spend Bucket]@row = "Blue Badge", [Spend Bucket]@row = "Red Badge"), -1, ""))))

    I've also tried both of the above with no luck

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    Hi @mtetzlaf

    Try this...

    =if(or( [Spend Bucket]@row = "Blue Badge", [Spend Bucket]@row = "red Badge"), if([Cost Incurred / Hired]@row = "Remove Cost", -1, 1), "")


    Keep in mind this formula is looking for "Blue Badge" and "red Badge" and "Remove Cost" specifically, case sensitive. If you have these in dropdown columns, the formula will need to be modified to include the HAS function.

    Please let me know if this worked.

    Ryan

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • mtetzlaf
    mtetzlaf ✭
    Answer ✓
    Options

    Thankyou!

    Just before you sent this I was able to figure it out with this formula:

    =IF([Cost Incurred / Hired]@row <> "Remove Cost", (IF(OR([Spend Bucket]@row = "Blue Badge", [Spend Bucket]@row = "red Badge"), 1, "")), IF([Cost Incurred / Hired]@row = "Remove Cost", IF(OR([Spend Bucket]@row = "Blue Badge", [Spend Bucket]@row = "Red Badge"), -1, "")))

    Though, yours is much simpler 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!