"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!

Find more posts tagged with

Formulas

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.