Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, pleaseVisit the Current Forums.

Money format in cell not working

Daniel Thornton
Daniel Thornton ✭
edited 12/09/19 inArchived 2015 Posts

I am using this formula to output a fee based on gross loss for insurance claim. The resulting total column shows proper dollar format when it is over $100,000 based on first IF statement. When it is either of the following IF statements, ie for losses > 500, or losses > than 0, then the "TOTAL" cell shows a dollar sign but is missing the coma at the thousand point, and the 2 zeros after the decimal point is not there either.

=IF([GROSS LOSS]2 > 100000, 0.024 * [GROSS LOSS]2, IF([GROSS LOSS]2 > 500, "290", IF([GROSS LOSS]2 > 0, "148")))

Thank you in advance for any ideas to help correct this!

Tags:
· Share on Facebook Share on Twitter

Comments

  • Travis
    Travis Employee

    Daniel - when you use quotes in a formula, the resulting value will be formatted as text, regardless of the content. This means “148” and “290” are seen as text values and not numbers and will not adhere to your currency format. Try removing the quotes from your formula and see if the resulting value is shown as currency.

    · Share on Facebook Share on Twitter
This discussion has been closed.

Categories

If you want to know the percentage over\/under the Contract Amount<\/strong>, your formula (placed the [Percentage] column) would be:<\/p>

=([Contract amount]@row - [Install Labor (actual)]@row) \/ [Contract amount]@row<\/p>

Be sure the \"Percentage\" column is formatted as a percentage. Positive numbers show that your total spend is under<\/strong> the [Contract amount]. Negative values show your total spend is over<\/strong>.<\/p>

You can use a similar formula to measure how far over\/under your [Labor $ (quoted)] amount is from your [Install Labor (actual)] amount.<\/p>

=([Labor $ (quoted)]@row - [Install Labor (actual)]@row) \/ [Labor $ (quoted)]@row<\/p>

Here, though, a negative value shows that you are OVER<\/strong> the estimate. A positive value shows you are at or UNDER<\/strong> the estimate.<\/p>

\n
\n \n \"Screenshot<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107038,"type":"question","name":"Modified Date loses detail when referenced","excerpt":"Application: Trying to capture and display a 'sheet last modified' value on a dashboard. Approach: Using a formula in the sheet summary sidebar to find the max value of all timestamps in the 'Modified' column. Formula is as follows and is functioning as expected. =MAX([Modified]:[Modified]) Problem: The displayed value…","snippet":"Application: Trying to capture and display a 'sheet last modified' value on a dashboard. Approach: Using a formula in the sheet summary sidebar to find the max value of all…","categoryID":322,"dateInserted":"2023-06-28T17:43:23+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T21:44:02+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T21:44:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154049,"lastUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T21:44:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":18,"score":null,"hot":3375964045,"url":"https:\/\/community.smartsheet.com\/discussion\/107038\/modified-date-loses-detail-when-referenced","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107038\/modified-date-loses-detail-when-referenced","format":"Rich","lastPost":{"discussionID":107038,"commentID":382970,"name":"Re: Modified Date loses detail when referenced","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382970#Comment_382970","dateInserted":"2023-06-28T21:44:02+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T21:44:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T21:29:15+00:00","dateAnswered":"2023-06-28T18:46:15+00:00","acceptedAnswers":[{"commentID":382932,"body":"

Set the Sheet Summary field as text\/number then add +\"//www.santa-greenland.com/community/discussion/181/\" to the end of the MAX function (plus quote quote) to convert it into a text string.<\/p>

=MAX([Modified]:[Modified]) + \"//www.santa-greenland.com/community/discussion/181/\"<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107030,"type":"question","name":"How to subtract percentages?","excerpt":"I created a new column % Not complete and am trying to add a formula to have =100%-[%Complete]@row. Answer #UNPARSEABLE I have also tried =100-%Complete]@row. Answer: This will populate an answer, however I cannot adjust the formatting from % to number Example: %Complete = 13% Correct answer should display 87% Second…","snippet":"I created a new column % Not complete and am trying to add a formula to have =100%-[%Complete]@row. Answer #UNPARSEABLE I have also tried =100-%Complete]@row. Answer: This will…","categoryID":322,"dateInserted":"2023-06-28T15:56:57+00:00","dateUpdated":"2023-06-28T16:22:44+00:00","dateLastComment":"2023-06-28T16:35:48+00:00","insertUserID":150369,"insertUser":{"userID":150369,"name":"v.winters","url":"https:\/\/community.smartsheet.com\/profile\/v.winters","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!p_DnqRu23us!e98n5_-JJOs!fH1r3mTuHpU","dateLastActive":"2023-06-28T16:34:54+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":150369,"lastUserID":150369,"lastUser":{"userID":150369,"name":"v.winters","url":"https:\/\/community.smartsheet.com\/profile\/v.winters","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!p_DnqRu23us!e98n5_-JJOs!fH1r3mTuHpU","dateLastActive":"2023-06-28T16:34:54+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":23,"score":null,"hot":3375939165,"url":"https:\/\/community.smartsheet.com\/discussion\/107030\/how-to-subtract-percentages","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107030\/how-to-subtract-percentages","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107030,"commentID":382885,"name":"Re: How to subtract percentages?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382885#Comment_382885","dateInserted":"2023-06-28T16:35:48+00:00","insertUserID":150369,"insertUser":{"userID":150369,"name":"v.winters","url":"https:\/\/community.smartsheet.com\/profile\/v.winters","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!p_DnqRu23us!e98n5_-JJOs!fH1r3mTuHpU","dateLastActive":"2023-06-28T16:34:54+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T16:35:51+00:00","dateAnswered":"2023-06-28T16:31:26+00:00","acceptedAnswers":[{"commentID":382883,"body":"

@v.winters<\/a> <\/p>

If the two percent columns are formatted as percent columns (see screenshot), then the % Not Complete column would have this formula: <\/p>

=1 - [%Complete]<\/em>@row<\/p>

\n
\n \n \"Menu.PNG\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&siteSectionID=0&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending Posts

  • Formula for %

    Im looking for a formula to return the percentage that our projects are over/under.

  • Modified Date loses detail when referenced

    Application: Trying to capture and display a 'sheet last modified' value on a dashboard. Approach: Using a formula in the sheet summary sidebar to find the max value of all timestamps in the 'Modified' column. Formula is as follows and is functioning as expected. =MAX([Modified]:[Modified]) Problem: The displayed value…

  • How to subtract percentages?

    I created a new column % Not complete and am trying to add a formula to have =100%-[%Complete]@row. Answer #UNPARSEABLE I have also tried =100-%Complete]@row. Answer: This will populate an answer, however I cannot adjust the formatting from % to number Example: %Complete = 13% Correct answer should display 87% Second…