Percentage Formula

Hargreaves

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

Darren Mullen

@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.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/344087#Comment_344087

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.)

Darren Mullen

@Jeff Reisman Ahh, good suggestion!