Strange Request of Rounding Down against Laws of Mathematics

Jack Parry
Jack Parry ✭✭✭✭
edited 09/07/23 in Formulas and Functions

I've had a strange request. One that defines the laws of Mathematics:

I'm calculating the time difference and then using this formula to round down. However, the formula is following the laws of mathematics and rounding up when it sees an 8 or 9. 

=MROUND(ROUNDDOWN(Difference@row), 5)


In this example, the requestor needs to round down to 55 always.

Any suggestions?


Best Answer

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    Answer ✓

    Hello @Jack Parry

    =IF([Difference]@row < MROUND([Difference]@row, 5), MROUND(([Difference]@row - 5), 5), MROUND([Difference]@row, 5))

    This equation first specifies whether the value was rounded up or down, based on the multiplier. If it was greater, than subtract 5 (being the multiplier) and round that value to the nearest multiple.

    This function will ALWAYS round down unless the value is already a multiple of 5.

    If the value in the cell is 54, the result will be 50. Not 55.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!