Can't get this nested IF statement to work
=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
-
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
-
=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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.1K Get Help
- 450 Global Discussions
- 155 Industry Talk
- 505 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 156 Just for fun
- 80 Community Job Board
- 514 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!