Skip to main content

Hi AirTable Community -


I have a grid with one single select column indicating contributors (Freelance Writers) and another column indicating Word Count (#). The Word Count field will be an integer, but the cost calculation will depend on where the word count falls within a numerical range. I’m hoping to generate a formula that generates the cost of the article based on the word count. It would read like this:


If {Freelance Writer} is “option from single select column” and {Word Count} >=500 and {Word Count} is <=700, Cost “150”


but I am striking out. Any advice on what I’m missing?

Hi @Lieren_Stuivenvolt_A,


I assume you will have multiple options within {Freelance Writer} and also multiple options within {Word Count}, so to make that a little less messy, we can use a SWITCH() function in combination with IF() statements, or even nested SWITCH() functions. I think something like this should work:


SWITCH(

    {Freelance Writer},

    "Option 1",

    IF(

        {Word Count} >= 1500,

        300,

        IF(

            {Word Count} >= 1000,

            250,

            IF(

                {Word Count} >= 750,

                200,

                IF(

                    {Word Count} >= 500,

                    150,

                    100

                )

            )

        )

    ),

    "Option 2",

    IF(

        {Word Count} >= 1200,

        260,

        IF(

            {Word Count} >= 900,

            240,

            IF(

                {Word Count} >= 700,

                200,

                150

            )

        )

    )

)

The SWITCH() function evaluates the first parameter you pass it - in this case we told it to look at the {Freelance Writer} field - and matches it against the output options you pass it in subsequent parameter spots. In this case, we passed "Option 1" and "Option 2" as the possible values of the {Freelance Writer} field we want to act against.


If the SWITCH() function determines that the field {Freelance Writer} matches the output "Option 1", it will execute the nested IF() statement we made right after "Option 1" – and that nested IF() is running down possible options for the output of the {Word Count} field. When one of those conditions matches, you get your final output for the field. I put the {Word Count} options in descending order (the largest word count threshold - 1500 - first) so that they can be evaluated simply without having to define both an upper and lower limit for each threshold; if {Word Count} isn’t >= 1500, then it moves down into the next threshold category, namely >=1000, and if it doesn’t meet that threshold then it moves down into the next, until it finds a hit and returns your dollar amount.


Hopefully that makes sense enough that you can plug in your own data and expand on it.

Jeremy, you’re a wizard! Absolutely works:


SWITCH({Freelance Writer}, “Carrie”, IF({Word Count} >= 2501, 650, IF({Word Count} >= 2101, 575, IF({Word Count} >= 1801, 500, IF({Word Count} >= 1501, 425, IF({Word Count} >= 1301, 350, IF({Word Count} >= 1001, 275, IF({Word Count} >= 701, 200, IF({Word Count} >= 500, 150)))))))), “Vanessa”, IF({Word Count} >= 1501, 350, IF({Word Count} >= 1301, 275, IF({Word Count} >= 1001, 200, IF({Word Count} >= 701, 150, IF({Word Count} >= 500, 125))))))


Thank you so so much!

Reply


Terms & Conditions