Can't get this nested IF statement to work

mtetzlaf

=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?

 

mtetzlaf

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 🙂

mtetzlaf

=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

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

mtetzlaf

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 🙂