"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
Check out the Formula Handbook template!