Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Multiple IF statements after searching for blank field

Topic Labels: Formulas
Solved
Jump to Solution
477 3
cancel
Showing results for 
Search instead for 
Did you mean: 

I currently have an IF statement formula nested into a longer SWITCH formula, and am looking for a way to modify it some. Currently it is

IF({Total Order Sold Price} < 15, 2.95,
IF({Total Order Sold Price}>= 15, {Sold Price} * .2))

I want to first check for data in the {Total Order Sold Price} field. If there is data, I want it to run this set of IF statements. If there is no data, I want it to run the same IF statements, with a slight modification:

IF({Sold Price} < 15, 2.95,
IF({Sold Price}>= 15, {Sold Price} * .2))

I’ve tried writing it a few different ways, the best I came up with is this:

IF({Total Order Sold Price}, IF({Total Order Sold Price} < 15, 2.95, IF({Total Order Sold Price}>=15, {Sold Price} * .2))),
IF({Total Order Sold Price}=BLANK(), IF({Sold Price} < 15, 2.95, IF({Sold Price}>=15,{Sold Price} * .2)))

Nothing is working so far though. Any help is appreciated

1 Solution

Accepted Solutions

I recommend using a multi-line syntax. especially since you want to nest this inside a SWITCH function.

Here is one formula that directly translates your words into Airtable’s formula language.

IF(
    {Total Order Sold Price} & "",
    IF(
        {Total Order Sold Price} < 15,
        2.95,
        {Sold Price} * .2
    ),
    IF(
        {Sold Price} < 15,
        2.95,
        {Sold Price} * .2
    )
)

However there are also other ways of getting the number you want.

IF(
    OR(
        {Total Order Sold Price} >= 15,
        {Sold Price} >= 15
    ),
    {Sold Price} * .2,
    2.95
)

Use what makes the most sense to you and will be the easiest for you to maintain.

See Solution in Thread

3 Replies 3

To keep things simple, I would crreate 2 formula fields instead of one.

For the first formula field, I would just figure out which number to use:

IF(
{Total Order Sold Price},{Total Order Sold Price},
{Sold Price}
)

Then, for your 2nd formula field, you can use your original formula but just point to the new formula field above.

I recommend using a multi-line syntax. especially since you want to nest this inside a SWITCH function.

Here is one formula that directly translates your words into Airtable’s formula language.

IF(
    {Total Order Sold Price} & "",
    IF(
        {Total Order Sold Price} < 15,
        2.95,
        {Sold Price} * .2
    ),
    IF(
        {Sold Price} < 15,
        2.95,
        {Sold Price} * .2
    )
)

However there are also other ways of getting the number you want.

IF(
    OR(
        {Total Order Sold Price} >= 15,
        {Sold Price} >= 15
    ),
    {Sold Price} * .2,
    2.95
)

Use what makes the most sense to you and will be the easiest for you to maintain.

Thank you! This worked!