Invalid operation on formula that's been working

System

This discussion was created from comments split from: Intermittent #Invalid Operation error.

CFJ

I have been using this formula for over a year and now I am getting this "Invalid Operation" error. Is Smartsheet working in the background now causing this?

CFJ

This the formula that has been work for a long time even when Copy from another cell and paste it, I still get the error. I've check all the formats and can not find the issue

Paul Newcome

Are you manually entering the $ in the [Material Price] column? You should have it set as a text/number column and only enter the numbers themselves then have the column formatted to show as dollars.

CFJ

Thanks Paul for the response. We are not manually entering the $ in the material price, it is set up in my automation as $ and the (all) relevant column(s) properties are set up as Text/Number. It has been working this way for over a year and it just suddenly it's give an error. The products are set up in a drop-down menu that triggers the [Material Price] in $. So are saying a number(ton) and a $[Material Price] number can not be multiplied? Some products that are selected will work fine and others will not

CFJ

Hi Paul, Disregard my comment above. I found my issue. When setting up the Automation Material Price, I used a $ sign with the price. My Material Price Column was also set up as currency $. This must have been conflicting with the calculation. I removed the $ sign in the Automatic Material Price and just kept it as a number. I kept the Currency $ in the Column formatting and everything is working great again. Your help made me think about the column formatting and my automation pricing formatting and how it could effect the calculations. Thank you!!

Paul Newcome

The $ being added by the automation is the same as being manually added. It is a "special character" which converts the string into text. That is why it was not working. Because you can't multiple a number by a text string.

Getting rid of the $ and leaving the column formatted as currency allowed Smartsheet to only display the $ but store it as a number on the back-end which in turn means multiplying a number by another number which does work.