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
-
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
-
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.
-
Thank you, that worked!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!