How do I make a word in one column equal a dollar value in another column?

I’ve tried the the IF formulas but can’t seem to get it right. This is the formula I’ve tried after reading some answers from another question, but I receive #INCORRECT ARGUMENT SET.

=IF(CONTAINS(“NEW”, [New or Reload]@row, “$400”, IF(CONTAINS(“RELOAD”, [New or Reload]@row, “$200”, “”))))

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You are forgetting to close out your CONTAINS functions.

    =IF(CONTAINS("NEW", [New or Reload]@row),


    Your use of quotes is also going to cause some issues for two reasons.

    Reason 1 (will throw the #UNPARSEABLE error message):

    See how yours are slanted in your post, but here in my comment mine are straight up and down? Those slanted ones are called "smart quotes" which (ironically enough) are not considered valid characters in a Smartsheet formula. You are going to want to retype them here, directly in Smartsheet, or in a text editor such as Notepad (not Word).


    Reason 2 (can cause headaches later on but won't necessarily throw an error):

    Putting quotes around your numbers like so "$400" will output a TEXT string that only looks like a dollar amount. Remove the quotes and the $ and just output the number 400 then format your column to display as dollars.

    "$400"

    changes to

    400

    The reason this can become an issue is that text values cannot be used in numeric calculations such as adding things up or getting averages. It will display just fine, but if you need to ruin any kind of summary data / metrics on it, you are going to get errors and zeros.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You are forgetting to close out your CONTAINS functions.

    =IF(CONTAINS("NEW", [New or Reload]@row),


    Your use of quotes is also going to cause some issues for two reasons.

    Reason 1 (will throw the #UNPARSEABLE error message):

    See how yours are slanted in your post, but here in my comment mine are straight up and down? Those slanted ones are called "smart quotes" which (ironically enough) are not considered valid characters in a Smartsheet formula. You are going to want to retype them here, directly in Smartsheet, or in a text editor such as Notepad (not Word).


    Reason 2 (can cause headaches later on but won't necessarily throw an error):

    Putting quotes around your numbers like so "$400" will output a TEXT string that only looks like a dollar amount. Remove the quotes and the $ and just output the number 400 then format your column to display as dollars.

    "$400"

    changes to

    400

    The reason this can become an issue is that text values cannot be used in numeric calculations such as adding things up or getting averages. It will display just fine, but if you need to ruin any kind of summary data / metrics on it, you are going to get errors and zeros.

  • MaWolf
    MaWolf ✭✭

    Thank you, that worked!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!