Nested IF formula with AND and OR components

[dance_teacher5]

I’m new to Airtable and databases in general.
My database will be used to organize a dance competition. I’m trying to construct a formula to calculate how many medals I will need for each competition. The number of medals needed depends on the type of dance and the number of competitors, a few of which are shown in the image below:

My formula keeps returning an error (Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.). I have been through many Airtable articles, tutorials, and forum posts, and still do not know what I have done wrong. Any help is much appreciated.

IF(
AND(
OR({Dance} = “Reel”, {Dance} = “Slip Jig”, {Dance} = “Single Jig”, {Dance} = “Light Jig”, {Dance} = “Treble Jig”, {Dance} = “Hornpipe”, {Dance} = “St Patricks Day”, {Dance} = “Humours of Bandon”, {Dance} = “Job of Journeywork”, {Dance} = “Blackbird”, {Dance} = “Garden of Daisies”, {Dance} = “White Blanket”),
{Number Entered}>=4),
1,
IF(
AND({Dance} = “Reel & Set”, {Number Entered}>=2),
1,
IF(
AND({Dance} = “2-Hand Reel”, {Number Entered}>=4),
2,
IF(
AND(
OR({Dance} = “3-Hand Reel”, {Dance} = “Family Dance”),
{Number Entered}>=4),
3,
IF(
AND(
OR({Dance} = “4-Hand Reel”, {Dance} = “Walls of Limerick”),
{Number Entered}>=4),
4,
IF(
AND(
OR({Dance} = “Ceili”, {Dance} = “Ceili Club & Folk”),
{Number Entered}>=4),
8,
0))))))

[ScottWorld · Accepted solution]

Welcome to the community, @dance_teacher5! :slightly_smiling_face:

This forum changes all quotation marks to curly quotes unless you put the backtick (`) around your code or unless you use the “Preformatted Text” option.

So, I’m not sure if your actual formula really has curly quotes or not, but…

Once I changed your formula to use straight quotes instead of curly quotes, your formula worked just fine for me.

After I changed all your quotes to straight quotes, I just copied-and-pasted your formula into my Airtable base, and it worked fine without any error messages.

For some reason, Airtable’s formula engine can’t interpret curly quotes — it requires straight quotes. I would really love for them to change this behavior! It’s very frustrating.

To quickly change all of your quotes on my Mac, I just copied and pasted your text into the BBEdit text editor on the Mac, and then I chose the menu option “Text > Straighten Quotes”.

Here’s the result with straight quotes:

IF(
AND(
OR({Dance} = "Reel", {Dance} = "Slip Jig", {Dance} = "Single Jig", {Dance} = "Light Jig", {Dance} = "Treble Jig", {Dance} = "Hornpipe", {Dance} = "St Patricks Day", {Dance} = "Humours of Bandon", {Dance} = "Job of Journeywork", {Dance} = "Blackbird", {Dance} = "Garden of Daisies", {Dance} = "White Blanket"),
{Number Entered}>=4),
1,
IF(
AND({Dance} = "Reel & Set", {Number Entered}>=2),
1,
IF(
AND({Dance} = "2-Hand Reel", {Number Entered}>=4),
2,
IF(
AND(
OR({Dance} = "3-Hand Reel", {Dance} = "Family Dance"),
{Number Entered}>=4),
3,
IF(
AND(
OR({Dance} = "4-Hand Reel", {Dance} = "Walls of Limerick"),
{Number Entered}>=4),
4,
IF(
AND(
OR({Dance} = "Ceili", {Dance} = "Ceili Club & Folk"),
{Number Entered}>=4),
8,
0))))))

Hope this helps! If this solves your problem, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:

Also, if this solves your problem, could you also chime in on this thread, where I suggested this feature to Airtable: Formula engine recognizing curly quotes

[dance_teacher5]

Well that was simple! Can’t believe I spent hours trying to figure that out… I was aware of the curly vs straight quotes issue, but didn’t think it was my problem because when I first copied my formula into Google Docs to work on it, the quotes were straight. Now that I look at subsequent iterations in my Google Doc, I see that there they are curly. DOH!

Thanks so much for your help. I will certainly tag onto the feature suggestion as well.

[ScottWorld]

You are welcome!! So glad that it was such an easy solution for you! :slightly_smiling_face: Also, I forgot to mention this earlier — but if you can also please send an email message to support@airtable.com about this curly quotes issue, that would be very helpful too! :slightly_smiling_face: As you can tell, I’m a big advocate for the Airtable team to finally fix this very annoying issue! :winking_face:

[Justin_Barrett]

@dance_teacher5 I strongly suggest finding a different tool for editing formulas. Editors like Google Docs, Word, OpenOffice, etc. treat everything as styled text by default. As you’ve seen, that can mess up an otherwise clean formula. What you should look for is a plain text editor, or better yet a code editor. I’m on a Mac, and use BBEdit. Windows comes with Notepad, which will do the job, though in my Windows days I used EditPad, which comes in both light and pro versions. There are lots of others, and I recommend taking some time to find one that works for your needs.

[dance_teacher5]

That’s a great idea, thanks!