"INVALID DATA" Error

hollybarnard

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!

markkrebs

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