Skip to main content

Help with a nested if formula

  • March 29, 2021
  • 1 reply
  • 13 views
J

I am trying to use a formula to return the value of a sponsorship package for a golf tournament. I have a column called “Sponsorship” and under that column have values “Level 1, Level 2, Level 3, Level 4, Level 5” I want to use this formula but it keeps giving an error so I must be doing something wrong. Would appreciate any help.

IF({Sponsorship}= “Level 1”,“$5,000”,
IF({Sponsorship }= “Level 2”, “$10,000”,
IF({Sponsorship }= “Level 3”,“$15,000”,
IF({Sponsorship }= “Level 4”,“$20,000”,
IF({Sponsorship }= “Level 5”,“$25,000”)))))

Thanks,
Jim

1 reply

Justin_Barrett
Forum|alt.badge.img+21

Welcome to the community, @Jim_Richardson1! :grinning_face_with_big_eyes: The thing that stands out to me are the spaces on four of the lines between the end of the word “Sponsorship” and the closing curly brace. If the field doesn’t have a space after “Sponsorship,” that could be throwing Airtable off the scent.

Field names that are single words without special characters don’t need the surrounding curly braces. This should work:

IF(Sponsorship = "Level 1", "$5,000",
IF(Sponsorship = "Level 2", "$10,000",
IF(Sponsorship = "Level 3", "$15,000",
IF(Sponsorship = "Level 4", "$20,000",
IF(Sponsorship = "Level 5", "$25,000")))))

If that doesn’t clean things up, check the quotes around your string. If you copied a sample formula from someplace that used styled quotes, that will throw an error. I always format formulas using the “Preformatted text” option in the forum to prevent styled quotes from coming into play.

On a side note, I noticed that your output is a string. Do you want a numeric value, or is the string sufficient? I can’t tell from your comments if you need that number for other calculations, so I thought that I’d throw this out there.

On another side note, this could be simplified by using SWITCH() instead of nested IF() functions. The SWITCH() function is designed specifically for cases where you’re switching the output depending on the value found in a single input. Here’s that same formula using SWITCH():

SWITCH(
  Sponsorship,
  "Level 1", "$5,000",
  "Level 2", "$10,000",
  "Level 3", "$15,000",
  "Level 4", "$20,000",
  "Level 5", "$25,000"
)
Terms & ConditionsAccessibility statement