"INVALID DATA" Error
I've written out this formula numerous ways at this point and I think I'm just trying to work outside of my current abilities. I'm trying to write an "IF" formula to calculate the cost of shipping materials because there are 4 different scenarios that I need to calculate for:
Scenario 1: No shipping material used ($0). Inlay Status = NA and LF <12.
Scenario 2: Just lumber is used (LF * $0.84). Inlay Status = NA and LF 12.
Scenario 3: Just a shipping tube is used (LF * $7.42). Inlay Status does NOT = NA and LF <12.
Scenario 4: Lumber and a shipping tube are used (LF * $8.26). Inlay Status does NOT = NA and LF
My current formula works it's scenario 1, but comes up as "INVALID DATA" for the other scenarios.
==IF(AND([Inlay Status]@row = "NA", LF@row < 12), "0", IF(AND([Inlay Status]@row, LF@row > 12), LF@row * 0.42, IF(AND(LF@row < 12, NOT([Inlay Status]@row = "NA")), LF@row * 7.42, IF(AND(LF@row > 12, NOT([Inlay Status]@row = "NA")), LF@row * 7.84))))
Any advice or help would be greatly appreciated!
Answers
-
=IF(AND([Inlay Status]@row = "NA", LF@row < 12), 0, IF(AND([Inlay Status]@row = "NA", LF@row = 12), LF@row * 0.82, IF(AND(LF@row < 12, [Inlay Status]@row = "Not Started"), LF@row * 7.42, IF(AND(LF@row > 12, [Inlay Status]@row = "Not Started"), LF@row * 7.84))))
The issues were the bolded parts. Very close! I assumed scenario #2 was LF = 12
Also, I only corrected the logic and not the math - you have .84 and not 8.4 as the multiplier. Not sure if that was intentional or a miskey.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!