Percentage Formula

Options
edited 09/20/22

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

@Jeff Reisman Ahh, good suggestion!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!