IF function with several “than” options is “UNPARSEABLE”

IBG_rs

I’m trying to use the formula below in a sheet to autofill one of several values based on a certain dollar amount/criteria. Logic: If “NAP” cell is between 2500-4999 then have target cell say “Builder’s Week”, and if “NAP” cell is between 5000-7499 then have target cell say “Green Out”…with several more options like this.

Formula I’ve tried (but coming back as UNPARSEABLE):

=IF([NAP@row] >= 2500, "Builder’s Week", [-], IF([NAP@row] >= 5000, “Green Out”, IF([NAP@row] >= 7500, “Globe Week”, IF([NAP@row] >= 10000, “Eagle”))))

Thanks, in advance, for your help. None of the many previous discussions I read seemed to address this.

Nic Larsen

I think this character ([-]) is causing your unparseable error after the first IF statement. But you will also have issues with the order because it's unlikely the rest of the statement will happen if anything is greater than 2500, they'll all show as Builders Week. Try it like this:

=IF([NAP@row] >= 10000, “Eagle”, IF([NAP@row] >= 7500, “Globe Week”, IF([NAP@row] >= 5000, “Green Out”, IF([NAP@row] >= 2500, "Builder’s Week"

Jason Tarpinian

=IF([NAP@row] >= 2500, "Builder’s Week", [-], IF([NAP@row] >= 5000, “Green Out”, IF([NAP@row] >= 7500, “Globe Week”, IF([NAP@row] >= 10000, “Eagle”))))

The "[-]" piece of your formula is the first IF statements false return. It's returning UNPARSEABLE because it doesn't know what to do, because the formula should be done by then. Remove the [-], (including 1 of the commas) as I've stricken above and you should be good to go.

KPH

Hi

There are three problems with the syntax:

1. I am not sure what this part is: [-]
2. You have some "smart" quotation marks - curved ones. You need to use straight ones (like the ones around Builder's Week
3. You do not need the square brackets around [NAP@row]. These go around the column name only. So this would be OK [NAP]@row but as your column name does not contain spaces, you do not need them at all.

This is the correct syntax

=IF(NAP@row >= 2500, "Builder’s Week", IF(NAP@row >= 5000, "Green Out", IF(NAP@row >= 7500, "Globe Week", IF(NAP@row >= 10000, "Eagle"))))

If you correct that the formula will pass however, it will not do what you want.

The If syntax is

=IF(logic, value if true, value if false)

You are nesting the IFs in the value if false space. So your second IF will only be evaluated if the first is false.

In other words, if NAP is NOT greater than or equal to 2500, it will look at the second IF.

The second IF checks if NAP is greater than or equal to 5000. Which it won't be, as it is not greater than 2500.

If you swap the order around so the larger amount is first it will do what you need.

=IF(NAP@row >= 10000, "Eagle", IF(NAP@row >= 7500, "Globe Week", IF(NAP@row >= 5000, "Green Out", IF(NAP@row >= 2500, "Builder’s Week"))))

IBG_rs

Nic, Jason, and KPH, thanks much. The formula below worked! And to answer the question, what was [-], that was my original goal to put a dash if the result was false (or didn’t meet the minimum criteria. But having a blank cell if it doesn’t “qualify” works just as well.

=IF(NAP@row >= 10000, "Eagle", IF(NAP@row >= 7500, "Globe Week", IF(NAP@row >= 5000, "Green Out", IF(NAP@row >= 2500, "Builder’s Week"))))

KPH

Wow, I did not see those other comments come in as I was typing. We're like avengers today 😀

If you want a dash to appear if all the IFs are false, you can put it at the end like this:

=IF(NAP@row >= 10000, "Eagle", IF(NAP@row >= 7500, "Globe Week", IF(NAP@row >= 5000, "Green Out", IF(NAP@row >= 2500, "Builder’s Week", "-"))))

IBG_rs

OK, so I just completed the rest of the formula to include all 8 “levels” that we recognize (meaning that it contains 8 IF’s and ends with 8 closed parentheses), and now I’m getting an error message saying the “the column formula syntax isn’t quite right.”

=IF(NAP@row >= 30000, “Unstoppable Eagle”, IF(NAP@row >= 25000, “Soaring Eagle”, IF(NAP@row >= 20000, “Heritage Eagle”, IF(NAP@row >= 15000, “Leader’s Eagle”, IF(NAP@row >= 10000, "Flight of Eagle", IF(NAP@row >= 7500, "Globe Week", IF(NAP@row >= 5000, "Green Out", IF(NAP@row >= 2500, "Builder’s Week"))))))))

KPH

You still have some smart quote marks.  “ is a problem. " is OK. See how the first is slanted or curved? Smartsheet does not recognize that as a quote.

These are all problematic

• “Unstoppable Eagle”
• “Soaring Eagle”
• “Heritage Eagle”
• “Leader’s Eagle”

If you are copying from somewhere and pasting into the formula can you try pasting into a simple text editor, like notepad (not Word), first. That should swap the curved quotation marks for straight ones.

If you are using a Mac it may be occurring as you type. There is an option to switch off smart quotes.

This is the corrected formula (with the dash enhancement).

=IF(NAP@row >= 30000, "Unstoppable Eagle", IF(NAP@row >= 25000, "Soaring Eagle", IF(NAP@row >= 20000, "Heritage Eagle", IF(NAP@row >= 15000, "Leader’s Eagle", IF(NAP@row >= 10000, "Flight of Eagle", IF(NAP@row >= 7500, "Globe Week", IF(NAP@row >= 5000, "Green Out", IF(NAP@row >= 2500, "Builder’s Week", "-"))))))))

IBG_rs

KPH, thank you. I learn something new every day but today I was blessed with an extra helping of learning. Smart quotes were mentioned previously, but I didn’t understand what that was. Never heard of it before, so it just went right over my head. So, your very simple and helpful explanation that only helped me personally but solved the issue completely. All is working perfectly. Thank you!!

KPH

2 for 1 - a working formula and new skills!

Glad we could help!