Help with basic IF statement

[Pamela_Giles]

This seems like ridiculously easy formula to write, but I’m stymied.

IF({Paid} ≥ {120%}, ({Paid} - FMV),0)

If the person Paid more than 120% (120% is a column calculating 120% of the Fair Market Value), then the value of their tax receipt is the amount the Paid - FMV. Otherwise, their receipt is 0.

It seems so easy! But none of my attempts work. What am I missing?

Thanks.

[Jeremy_Oglesby]

How is this failing? Are you getting an error message? Or is the formula just not returning what you expect?

[Pamela_Giles]

It gives me error message “Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.”

[Jeremy_Oglesby]

IF Paid and FMV are field names, then I don’t see why this shouldn’t work – unless perhaps you are using a non-standard symbol for >=. Try this:

IF(
   Paid >= {120%},
   Paid - FMV,
   0
)

I’m just using the standard > and = keyboard characters combined to make “greater than or equal to”. Perhaps that is the problem?

[Pamela_Giles]

My gosh! That works!!!

I guess I could nest to include the = FMV as a separate line.

Thank you.

[ScottWorld]

Jeremy_Oglesby:

IF( Paid >= {120%}, Paid - FMV, 0 )

Wow, I didn’t realize that would work with the % sign in there! Nice! :slightly_smiling_face:

@Pamela_Giles, I think the problem with your original formula had something to do with the extra spaces throughout your formula. Airtable is very finicky about formulas having NO SPACES between “certain elements”. I’m not sure what those “certain elements” are, because it’s rather arbitrary.

Also, this probably didn’t matter in your case, but you don’t need to put the {} around field names that are only one word long.

When I changed your original formula of

IF({Paid} ≥ {120%}, ({Paid} - FMV),0)

to

IF(Paid>=120,Paid-FMV,0)

I was able to get it to work.

So I’m pretty sure it had something to do with the extra spaces. Maybe it also had something to do with the >= sign being a symbol instead of 2 separate characters. Hard to tell what was wrong because Airtable doesn’t specifically pinpoint WHICH PART of the formula is the problematic part.

[Pamela_Giles]

I put the spaces in because some of the examples in the help seemed to have them. The extra { }s went in in desperation.

It’s definitely the ≥ symbol that was the problem. I never would have figured that out on my own.

Thanks again.

[Jeremy_Oglesby]

ScottWorld:

something to do with the extra spaces.

@ScottWorld and @Pamela_Giles

For future reference, whitespace is completely irrelevant in Airtable formulas. You can put as much whitespace as you want (spaces, new lines, tabs) in a formula and Airtable’s formula processor will just trim it out or ignore it when it performs its operations. You’ll notice the formula I posted has newlines and tabs in it in order to give it a code-like structure, which I find easier to read and digest – I presume @Pamela_Giles just copy-pasted my formula, as it is, into her formula editor and it worked; and if she didn’t, well then at least she could have.

I write nearly all of my Airtable formulas in text editor (coding) software, rather than in Airtable’s formula field editor, so that I can format them as I did above – and then I just copy-paste my resulting formula into the Airtable formula field editor. I find it to be a much nicer experience, as the formula field editor is very finicky, and nearly always catches me trying to enter tabs or new lines.

The editor that Airtable built into the Scripting block, on the other hand… now that is a very nice experience, comparatively.

[ScottWorld]

Jeremy_Oglesby:

For future reference, whitespace is completely irrelevant in Airtable formulas.

No, this is not true.

For example:

IF( 3 > 0, "yes", "no" )

will evaluate just fine.

But

IF ( 3 > 0, "yes", "no" )

will result in an error.

I have never before worked with a single programming language that would get hung up on that extra space there!

However, this is where things gets really strange.

Because Airtable got hung up on that extra space up above, you would think that it would get hung up on an extra carriage return — but it doesn’t.

For example, this function evaluates just fine:

IF
(3 > 0, "yes", "no" )

But sure enough, if you add an extra space on that 2nd line like this, then it produces an error message:

IF
_(3 > 0, "yes", "no" )

(The underscore above represents the extra space, because this discussion board won’t let me add a space at the beginning of a line.)

But — the good news here is that I’m starting to figure out that there IS some consistency to where Airtable refuses to allow spaces — and it seems to be near the beginning part of each function.

I’ve been programming in FileMaker Pro for 30+ years, and all spaces are irrelevant… so I’ve never encountered anything like this before. In FileMaker, all 4 of the above scenarios are evaluated completely 100% identically.

I almost gave up on Airtable in my early days, because I couldn’t get any of the formulas to work! Lol.

Interestingly enough, FileMaker also understands that ≥ and >= are the same thing, so @Pamela_Giles would never have had her problem to begin with! :stuck_out_tongue_winking_eye:

I totally love Airtable as a lightweight relational database system, but I do feel like the Airtable folks could do a “little more polishing up” on their end to account for these minor differences in typing formulas. :slightly_smiling_face:

[ScottWorld]

Or — even better — if Airtable gave us an error message that told us EXACTLY where the error took place within the formula.

That’s another thing that most programming languages (including FileMaker) will do for you — they will highlight or call out the part of the formula which is causing the problem.