IF Function

Queenie Yap · 08/14/19

Hi All,

I copied an IF function to another sheet with identical column names. For some reason, the function no longer works. Does anyone know if there were changes to the IF Function. Here is the formula that I copied:

=IF([Start Date]3 < [Baseline Start]3, "red")

I got an "#Invalid Operation"

I submitted a help ticket but haven't gotten a reply. Does anyone here know?

Thanks.

Andrée Starå · 08/14/19

Hi,

Have you double-checked the column types and names?

Here's more information about the error message.

#INVALID OPERATION

Cause

Operators (see Create and Edit Formulas for details on acceptable operators) in a formula aren't supported in Smartsheet or are mistyped. For example, this IF statement that returns the text "Low Stock" if a cell contains 25 or less...

=IF(Quantity1 =< 25, "Low Stock")

...has the "less than or equal" to operator in the wrong order; the correct order being <= to make the formula =IF(Quantity1 <= 25, "Low Stock")

Resolution

Check all operators to make sure they're not mistyped and are supported by Smartsheet. See Create and Edit Formulas for details on acceptable operators. The most common cause of this is typing <= (less than or equal to) and >= (greater than or equal to) operator combinations in the wrong order.

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Queenie Yap · 08/15/19

Thank you Andree for responding. Column type, column names and formulas are identical in both sheets. For some reason the formula doesn't work in the newer sheet. Just wondering if the programming was changes for the functions because there was an update done a few weeks ago.

Queenie

Paul Newcome · 08/15/19

I have had this happen before as well. If you edit the formula in the new sheet (remove the row reference or delete the first letter of a column name or something) to "break" it, leave the cell, the edit the formula again to "fix" what you "broke", you should be good to go.

Andrée Starå · 08/15/19

That's strange!

I'd be happy to take a look and see if I can see what's causing it.

Can you maybe share the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Andrée Starå · 08/15/19

Yes, I agree with Paul!

If you haven’t already, I would recommend that you try to break/fix the formula.

Queenie Yap · 08/15/19

OK, I figured it out. Both columns need to be the same column type. One of the columns I had it as TEXT column when both columns need to be DATE columns. Thanks for everyone's comments.

Queenie

Andrée Starå · 08/15/19

Glad you found the solution!

Happy to help!

Paul Newcome · 08/15/19

Yep. That'll do it. Glad you were able to get it working.

Formulas and Functions

Formulas and Functions

IF Function

Comments

Cause

Resolution

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Ideas to track schedule maintenance

Counting occurrences of a string in a row

Rounding to nearest Thursday in the past or current day if it's Thursday