IF, AND, OR Functions

dana y

So i have two formulas , one works and one does not; hoping someone can see what i am missing here.  i have four columns that i need to take into consideration before i  automatically check a checkbox .  using two of the conditions works; i add the third in the second example and it blows up.

thanks in advance:

works:

=IF(AND([Const Analyst Initial Check]39 = "Yes", OR([BACnet Analysts Initial Check]39 = "Yes-Ed", [BACnet Analysts Initial Check]39 = "Yes-Kevin")), 1, 0)

does not work:

=IF(AND([Const Analyst Initial Check]39 = "Yes",OR([First BACnet Router in Building?]39 =”Yes”, [First BACnet Router in Building?]39 = “No”),OR([BACnet Analysts Initial Check]39 = "Yes-Ed", [BACnet Analysts Initial Check]39 = "Yes-Kevin")), 1, 0)

Shaine Greenwood

Hi dana,

If you're getting an #UNPARSEABLE error on your second formula, it's likely due to the mixture of straight quotes vs curly quotes. Smartsheet can only understand straight quotes in formulas.

I'd recommend deleting those curly quotes (in the community they appear slanted in your formula) and manually typing the quotes again. Refrain from copying and pasting your formula from another source, including the community.

Aside from the quotes issue, the syntax of your formula appears correct. (Although I don't know the names of the columns you have in your sheet.)

If you still receive an error, please let me know the specific error you're getting or odd behavior, and I can advise further.

dana y

yes, that is exactly it i did not realize there were different types of quotes.

as recommended by another co-worker and a fella from smartsheets i have downloaded notepad ++ and marked it off as lesson learned.

 

thanks so much

McBry68

I'm having a similar problem with my IF AND function:

=IF([Total Inventory]@row >=5, "Green", IF(AND([Total Inventory]@row >=1, [Total Inventory]@row <5), "Yellow", IF([Total Inventory]@row =0, "Red", " ")))

I made sure the quotes were straight with no curly quotes but I'm still getting the "UNPARSEABLE" error. What change(S) to I need to make for this function to work as intended. Thank you.

Danielle Arteaga

@McBry68 - I tested your formula exactly as typed above, and it worked.

My [Total Inventory] column is a text/number column. My [Status] column is a symbol column (set to red/yellow/green option).

You may want to check that your column formats match mine and see if that fixes things for you.

You might also try simplifying your formula like this:

IF([Total Inventory]@row=0, "Red", IF([Total Inventory]@row>=5, "Green", "Yellow"))

IF statements are processed left to right, and the formula will stop processing as soon as it resolves as TRUE. So, if you have absolute values (like giving a Red status every time a value equals 0 or a Green status every time a value is equal to or greater than 5), you want to put those up front.

Hope this helps!

McBry68

@Danielle Arteaga I'm still getting the error even using your abbreviated version. Could that be because the total in "Total Inventory" is resulting from a formula? I'm pulling data from other Smartsheets to create the Total Inventory.

Danielle Arteaga

[Total Inventory] being the product of a formula shouldn't throw an error, and it's unlikely that the error would be #UNPARSEABLE. It would be more like #INVALID COLUMN VALUE.

But - let's check. Create a column to the right of [Total Inventory]. In the first cell, put this formula:

=IF(ISNUMBER([Total Inventory]@row), "YES", "NO")

If the result comes back YES, the formula is not the problem. If it comes back NO, then you'll need a workaround. (I can help if that's the case, but I'll spare you that headache for the moment.)

Usually an #UNPARSEABLE error is the result of something missing or out of order in your formula. Try actually typing the formula into the cell (rather than cutting/pasting) if you did not already, and double check your parentheses and brackets are actually parenthesis and brackets (not curly braces).

Also, maybe check the column type for the column where you are putting this formula to ensure that it is set to Symbol. (Although, even that shouldn't give you an error - your results would just be the actual words "Green", "Red" or "Yellow. Still - worth a shot?)

McBry68

@Danielle Arteaga Thank you. I typed the formula this time rather than copy/paste and it works. Have a wonderful day! 🙂