# "INVALID DATA" Error

Options
edited 11/16/23

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!

Tags:

• ✭✭✭✭✭✭
Options

=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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!