Percentage Formula
I am trying to obtain the percentage of the following:
Orders On Time
Orders In Full
Orders On Time in Full
My formulas are as follows:
Orders On Time - =ROUND(SUM([On Time (OT) %]1 / [Order Count]1)) + "%"
Orders In Full - =ROUND(SUM([In Full (OTIF) %]1 / [Order Count]1)) + "%"
Orders On Time in Full - =ROUND(SUM([On Time (OT) %]1 - 0 / [On Time (OT) %]1 + [Late %]1)) + "%"
Basically my Order On Time is not working and only showing 0% but removing the round gives a value of 21%
I enclose a screenshot of my test data
The figures to the right of the results is formula with out the round added for example
=SUM([On Time (OT) %]1 / [Order Count]1)
If my formulas \ maths are incorrect please advise as i would like to get this sorted.
Thanks
Answers
-
@Hargreaves You are rounding a decimal, so it is either going to go to 1 or 0. Multiply the sum by 100 to round, then you'll probably have to divide that again by 100 to get it back to a percentage.
I haven't thought out it too much, there might be a better way, but that should fix it for now.
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
Alternatively, add the number of decimal places into your ROUND syntax:
ROUND(number, [decimal_places])
And instead of adding the "%", leave your value as a decimal and format the cell for percentage. That way the value remains numeric. (Adding any value in quotes to a numeric value turns it into number stored as text, and you can no longer perform calculation on them.)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman Ahh, good suggestion!
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!