IF+AND Statement w Exclusion Conditions

[Lauren_Prete]

Hi all,

This has become a bit of a brainteaser for me and was wondering if anyone much more proficient than I could have a crack!

Trying to formulate a field to return COST PRICE as a NEG value when the following options are true:

1. There is a value in COST PRICE($) column
2. AND the REASON column value = “DEFECT”

Further, I need help creating a condition that applies the above only when the following is true for a certain supplier:

1. The SUPPLIER column value = VR
2. The GAME RETURNED column (checkbox) = TRUE

Essentially, I want to have a negative cost-price returned in a separate column if the REASON is listed DEFECT and the COST PRICE is AVAILABLE, but for a particular supplier (VR) I only need this negative cost-price value returned when the GAME RETURNED column is checked.

Current formula is:
IF({COST PRICE}=1, AND({REASON}=“DEFECT”), {COST PRICE}*-1)
and is returning cost-price for every reason code listed, instead of just DEFECT. Also doesn’t factor in the VR supplier condition.

Wondering if there is an Airtable wizard out there who can assist!

[Databaser]

Hi @Lauren_Prete and welcome to the community!

Try:

IF(AND({COST PRICE}, REASON=“defect”, SUPPLIER=“VR”, {GAME RETURNED}=1), 0-{COST PRICE})

[Lauren_Prete]

Databaser:

IF(AND({COST PRICE}, REASON=“defect”, SUPPLIER=“VR”, {GAME RETURNED}=1), 0-{COST PRICE})

Thanks @Databaser! Indeed a bit of wizardry going on here!

Unfortunately this formula is only returning results for REASON=DEFECT, SUPPLIER=VR, GAME RETURNED?=TRUE. I’d like to also return results for all other suppliers that have a defect and cost price listed.

I’m looking to return results for all DEFECT and COST PRICE entries, but in the case where the supplier is listed as VR in an entry related to a DEFECT, I would like to return results only when the GAME RETURN box is ticked.

Would I have to include a nested statement for this?

[Databaser]

This should work if I understand you correctly:

IF(AND({COST PRICE}, REASON="defect", SUPPLIER="VR", {GAME RETURNED}=1), 0-{COST PRICE}, IF(AND({COST PRICE}, REASON="defect", SUPPLIER!="VR"), 0-{COST PRICE}))

[kuovonne]

There are many possible formulas. Here is my suggestion.

IF(
    AND(
        {COST PRICE},
        {REASON} = "defect",
        OR(
            {SUPPLIER} != "VR",
            {GAME RETURNED}
        )
    ),
    -1 * {COST PRICE}
)

[Lauren_Prete]

Databaser:

IF(AND({COST PRICE}, REASON=“defect”, SUPPLIER=“VR”, {GAME RETURNED}=1), 0-{COST PRICE}, IF(AND({COST PRICE}, REASON=“defect”, SUPPLIER!=“VR”), 0-{COST PRICE}))

Thanks @Databaser this worked a treat!

@kuovonne thank you so much as well!