IF function help!

[Kate_Aldworth]

I would like to reduce the number of key depressions in my database. I have a number of clients and we have a different pay structure for each of them. I need a formula that would work something like this;

IF=Client [STR], “£15”
IF=Client [PRH], “£12.50”

But I can’t seem to get it to work. IF functions confuse the h*ll out of me!

Thanks in advance

Kate

[David_Skinner]

If statements simply execute the following logic:

If This Then That Else Other

The syntax for this in Airtable ( where we are checking the value in a column called “field”) is:

IF( {field} = This, That, Other )

So, in your case if you were just testing for one client called “STR”, and wanted it to be £15 for “STR” and blank in all other cases, your formula would be (assuming the field is called “Client”):

IF( {Client} = “STR”, 15, BLANK() )

But you want to test for multiple clients, so you can use nested IF statements. Basically, you can replace the Other in your original formula with another IF( {field} = This, That, Other ). So in your formula, you would replace the BLANK() with the next client condition IF( {Client} = “PRH”, 12.5, BLANK() ), to get:

IF( {Client} = “STR”, 15, IF( {Client} = “PRH”, 12.5, BLANK() ) )

You can continue this nesting process by replacing the BLANK() with another IF( {field} = This, That, Other ) to test for as many clients as you like.

[AlliAlosa]

To add to what @David_Skinner said, you could also use a SWITCH() function to accomplish what you’re looking for. When you’re only looking at the value of one field, SWITCH() is much cleaner than IF().

SWITCH({Client}, "STR", "£15", "PRH", "£12.50")

If you want the field to be formatted as a currency, though, you’ll want to remove the £ symbols and the quotes, and switch the formatting of the field to Currency.

SWITCH({Client}, "STR", 15, "PRH", 12.5)

Additional comment about IF statements… I generally try not to use BLANK() in mine as Airtable sometimes has trouble with that particular function. You can drop the second condition (“else”) of an IF statement and Airtable will implicitly know to return a blank value if the first condition doesn’t match. For example:

IF({Client} = "STR", 15)

[WilliamPorter]

Wow – the SWITCH() function is the bee’s knees, so long as you’re simply trying to match values against a single referent (like the OP’s Client field). Thanks for mentioning it @AlliAlosa. I wasn’t aware of it before today.

On the other hand, if you need to evaluate multiple and varying conditions, then you’re stuck with nested IFs. These shouldn’t really be THAT hard to write. These are some things I’ve noticed using Airtable’s formula editor.

1. Half of the problem is that Airtable’s formula editing area is miniscule and cannot be expanded. So unless you’re writing a very short formula, it’s good to use a separate editor to compose the formula, then copy and paste it into the Airtable formula editor.
2. The formula editor is finicky about spaces and line breaks. So get rid of blank spaces and line breaks in your external editor before you copy and paste.
3. Be careful about data types. If you’re writing a formula that calculates (say) a discount based on the value in the field Price, make sure that Price is a number field and not a text field!

Many other platforms I’ve worked with in the past provide better feedback about why a formula is faulty, but Airtable provides almost none. Using a good text editor (like BBEdit for MacOS, or Brackets, Sublime Text or Atom) won’t tell you that you’ve spelled a field name wrong or remind you that “=>” is not a valid operator, but the editor will at least help you balance your parentheses, and that’s pretty helpful.

Will

[Justin_Barrett]

WilliamPorterTech:

The formula editor is finicky about spaces and line breaks. So get rid of blank spaces and line breaks in your external editor before you copy and paste.

Spaces are only a problem in certain areas. The only area I’m aware of where there can’t be a space is between the name of a function and its opening parenthesis; i.e. IF(Comparison, "Yes") is valid, while IF (Comparison, "Yes") is not. Aside from that, though, I can’t think of an issue related to spaces.

Line breaks are also not a problem if you put them in the right place. I’m not sure where you’re placing them, but look around the forum and you’ll see that several users here (including me) like to share complex formulas broken across multiple lines. I always test my most complex formulas in Airtable before posting here, so they definitely work.

[WilliamPorter]

Thanks for that info @Justin_Barrett. I will watch more carefully for what works and what doesn’t and see if I can grasp the secret rules.

Will

[W_Vann_Hall]

I don’t think there are any secret rules other than what @Justin_Barrett mentioned: That is, no space between the function name and opening parenthesis.

While entering — or copy-and-pasting — a formula into the formula configuration field, Airtable sets the CSS white-space property to (I think) pre-wrap. Once you hit ‘Save’, it changes the value to normal, this changing how the formula is displayed. What’s important to note, though, is the formula itself isn’t changed; that is, Airtable doesn’t ‘suck out’ extraneous white-space characters. This is important to note, because it means when you copy-and-paste the formula from the configuration field back into your editor, all the original line breaks and indentation are maintained.

(At one point, I recall having issues with copy-and-paste if I hadn’t set my editor to replace tabs with spaces – but that might have been copying from the formula field and pasting into the field description…)

[WilliamPorter]

W_Vann_Hall:

I don’t think there are any secret rules other than what @Justin_Barrett mentioned: That is, no space between the function name and opening parenthesis.

I assume these are mentioned in the formula reference and I just missed them. Anyway, thanks to @Justin_Barrett and @W_Vann_Hall I will find it much easier now to write my formulas in a text editor.

But I’d note that my formulas don’t seem to make the round trip from text editor to Airtable and back without damage. Here’s a picture of a formula I was playing with. It’s not a real calculation but was designed as if I was trying to calculate a discount depending on the price of an item: the higher the price, the greater the discount.

The first version of the formula (all in one line, no spaces at all) is what I had arrived at earlier today before my post with my own observations about the formula editor. The second version is the nicely formatted version using line-breaks AND spaces but observing the grammar rules described by Justin and Vann. This is obviously MUCH clearer, indeed, this is I think pretty easy to follow. I copied the second version, pasted it into the formula editor in Airtable and yes! it was accepted.

But the third version is what I got when I copied the formula from Airtable and pasted it BACK into the editor (Brackets) for more editing. Alas, the line breaks are gone. Oh, well, just getting to the second version today was a major step forward. Thanks to all who helped (and I hope somewhere the OP got a question answered too).

[Justin_Barrett]

Hmmm…my hunch is that your editor is using a different combination of linefeed and carriage return codes than other editors when defining new lines. If you have other editors, you might check in the preferences of both those and Brackets and see if changing Brackets’ options can address this.

On a different note, your formula can be simplified a bit. Once you’ve confirmed that {theNumber} is not greater than or equal to 1000, it must automatically be less than 1000, so checking for that in the next nested IF() is unnecessary. Removing these redundancies, this should work:

IF(
    theNumber >= 1000,
    0.05,
    IF(
        theNumber >= 500,
        0.035,
        IF(
            theNumber >= 200,
            0.02,
            0
        )
    )
)

[W_Vann_Hall]

I think @Justin_Barrett’s hunch is probably right regarding EOL encoding. I use Notepad++ as an editor, and I typically have it set for Unix-style line endings (LF only) so I can edit, save, and upload files to my FreeBSD-based servers without having everything appear double-spaced. (This system is set for Windows style – CRLF – EOLs, so evidently copy-and-paste via the clipboard and browser is somehow standardized.) IIRC, the default EOL under MacOS/OSX is CR only, so you may want to see if Brackets allows LF or CRLF as an option.

And AFAIK, the no-space-between-function-and-parenthesis restriction isn’t explicitly spelled out in the Airtable docs; neither is anything pertaining to the use of external editors. (I, too, started out stripping extra white-space from formulas constructed offline; you can imagine my surprise — and delight — the first time I forgot and pasted an indented formula, only to find it worked. :winking_face: )