How to subtract percentages?

v.winters
v.winters ✭
edited 06/28/23 inFormulas and Functions

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 option displays 9,987%



I adjusted the value from 100 to 1 and it works! Hopefully this helps others too :)

Tags:
· Share on Facebook Share on Twitter

Best Answer

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
This is going to be the general idea. You will want to replace the name of the column that you use for the date of the observation. I also didn't know the name of the third observation type or the time period.<\/p>

=IF([Observation Type]@row = \"Safety\", WORKDAY([Observation Date]@row, 2), IF([Observation Type]@row = \"Opportunity to Improve\", WORKDAY([Observation Date]@row, 14), IF([Observation Type]@row = \"Other Type\", WORKDAY([Observation Date]@row, 365), \"//m.santa-greenland.com/community/discussion/comment/\")))<\/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":107920,"type":"question","name":"How to remove text to only leave numbers","excerpt":"I have a column with numbers and letters i.e 1.1a, 1.1b, 2.1a and so on and using a simple formula to remove the letter and leave number =LEFT([SubComp#]@row, (LEN([SubComp#]@row) - 1)) which works just fine, but for some reason the Conditional Formatting does not recognize the calculated output as a number to highlight…","snippet":"I have a column with numbers and letters i.e 1.1a, 1.1b, 2.1a and so on and using a simple formula to remove the letter and leave number =LEFT([SubComp#]@row, (LEN([SubComp#]@row)…","categoryID":322,"dateInserted":"2023-07-21T15:21:47+00:00","dateUpdated":null,"dateLastComment":"2023-07-21T15:31:04+00:00","insertUserID":96691,"insertUser":{"userID":96691,"name":"gwson","url":"https:\/\/community.smartsheet.com\/profile\/gwson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bb_am_gUV_w!XFjMnwg6CTQ!MSWtq5sIJTM","dateLastActive":"2023-07-21T15:30:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":96691,"lastUser":{"userID":96691,"name":"gwson","url":"https:\/\/community.smartsheet.com\/profile\/gwson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bb_am_gUV_w!XFjMnwg6CTQ!MSWtq5sIJTM","dateLastActive":"2023-07-21T15:30:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":22,"score":null,"hot":3379907571,"url":"https:\/\/community.smartsheet.com\/discussion\/107920\/how-to-remove-text-to-only-leave-numbers","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107920\/how-to-remove-text-to-only-leave-numbers","format":"Rich","lastPost":{"discussionID":107920,"commentID":386451,"name":"Re: How to remove text to only leave numbers","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386451#Comment_386451","dateInserted":"2023-07-21T15:31:04+00:00","insertUserID":96691,"insertUser":{"userID":96691,"name":"gwson","url":"https:\/\/community.smartsheet.com\/profile\/gwson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bb_am_gUV_w!XFjMnwg6CTQ!MSWtq5sIJTM","dateLastActive":"2023-07-21T15:30:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-21T15:35:25+00:00","dateAnswered":"2023-07-21T15:30:02+00:00","acceptedAnswers":[{"commentID":386449,"body":"

Try wrapping it in a VALUE()<\/p>

=VALUE(LEFT([SubComp#]@row, (LEN([SubComp#]@row) - 1)))<\/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":107898,"type":"question","name":"Using MAX to get latest date by criteria","excerpt":"Hello, I am trying get the latest date in a list, subject to criteria but MAX COLLECT does not seem to work. What am I doing wrong? I have tried both a text column and a date column. =MAX(COLLECT({CQ Hours Data Range 6}, {CQ Hours Data Range 2}, Model@row, {CQ Hours Data Range 5}, Batch@row),) Appreciate any suggestions!","snippet":"Hello, I am trying get the latest date in a list, subject to criteria but MAX COLLECT does not seem to work. What am I doing wrong? I have tried both a text column and a date…","categoryID":322,"dateInserted":"2023-07-21T11:41:31+00:00","dateUpdated":null,"dateLastComment":"2023-07-21T12:14:07+00:00","insertUserID":135590,"insertUser":{"userID":135590,"name":"Jbob","url":"https:\/\/community.smartsheet.com\/profile\/Jbob","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxY995A_sfSQHsmgUsRgqeK4EHeVdf9h7QM2bqHd=s96-c","dateLastActive":"2023-07-21T12:55:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":135590,"lastUser":{"userID":135590,"name":"Jbob","url":"https:\/\/community.smartsheet.com\/profile\/Jbob","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxY995A_sfSQHsmgUsRgqeK4EHeVdf9h7QM2bqHd=s96-c","dateLastActive":"2023-07-21T12:55:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":34,"score":null,"hot":3379882538,"url":"https:\/\/community.smartsheet.com\/discussion\/107898\/using-max-to-get-latest-date-by-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107898\/using-max-to-get-latest-date-by-criteria","format":"Rich","lastPost":{"discussionID":107898,"commentID":386369,"name":"Re: Using MAX to get latest date by criteria","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386369#Comment_386369","dateInserted":"2023-07-21T12:14:07+00:00","insertUserID":135590,"insertUser":{"userID":135590,"name":"Jbob","url":"https:\/\/community.smartsheet.com\/profile\/Jbob","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxY995A_sfSQHsmgUsRgqeK4EHeVdf9h7QM2bqHd=s96-c","dateLastActive":"2023-07-21T12:55:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-21T12:14:33+00:00","dateAnswered":"2023-07-21T12:03:58+00:00","acceptedAnswers":[{"commentID":386362,"body":"

HI @Jbob<\/a> <\/p>

Thanks for the post. I have tried to recreate your example (in a simple way) to see where the issue is.<\/p>

In mine the column called Date and the column called Max per Value are BOTH Date data types.<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

I have exposed the formula I have used in the last column to show how the Max per Value is coming through.<\/p>

I know this has simplified your original question, but can you see how the Max(Collect()) statement works here?<\/p>

Does this help at all?<\/p>

So in yours: <\/p>

=MAX(COLLECT({CQ Hours Data Range 6}, {CQ Hours Data Range 2}, Model@row, {CQ Hours Data Range 5}, Batch@row),<\/strong>)<\/p>

Range 6 and the column you have written this formula into must be Date fields and you have an extra comma where you don't need it! Just between the last two )). This comma will cause an error.<\/p>

Kind regards<\/p>

Debbie<\/p>"},{"commentID":386369,"body":"

Hello Debbie,<\/p>

Appreciate your help, that worked perfectly!<\/p>

Thank you!<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&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 in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • How do I calculate different due dates on actions depending on the type of action?

    I have created a sheet in which different type of issues are identified and assigned to owners. I have established three "observation types" that I would like to assign different due date. For example, a safety issues would need a due date of 2 working days from the date identified, and an Opportunity to Improve action…

  • How to remove text to only leave numbers

    I have a column with numbers and letters i.e 1.1a, 1.1b, 2.1a and so on and using a simple formula to remove the letter and leave number =LEFT([SubComp#]@row, (LEN([SubComp#]@row) - 1)) which works just fine, but for some reason the Conditional Formatting does not recognize the calculated output as a number to highlight…

  • Using MAX to get latest date by criteria

    Hello, I am trying get the latest date in a list, subject to criteria but MAX COLLECT does not seem to work. What am I doing wrong? I have tried both a text column and a date column. =MAX(COLLECT({CQ Hours Data Range 6}, {CQ Hours Data Range 2}, Model@row, {CQ Hours Data Range 5}, Batch@row),) Appreciate any suggestions!