Can it be done? Round "-" IF +

I can do this with a metic sheet but I would like to try and do it all in 1 formula. NOT sure it can be done!

I currently have a formula that gives me a number. Let's call it 17.35

I then round the number to split them up into 2 different cells

Row 1 = 17.35

Row 2 =ROUND([Column1]1, 0) Answer is 17 (In Excel I use TRUNC)

Row 3 =[Column1]1 - [Column1]2 Answer is 0.35

Row 4 =IF([Column1]3 < 0.25, [Column1]2, IF([Column1]3 > 0.75, [Column1]2 + 1, [Column1]2 + 0.5)) Answer is 17.50

Joe Goetschel | Smartsheet Director

[email protected]

Tags:

Best Answer

  • Dossy Shiobara
    Answer ✓

    It sounds like you want MROUND() which rounds to the nearest multiple, but your specific example's boundaries defined by your IF() is slightly different than MROUND()'s behavior.

    Try:

    =MROUND([Column1]1, 0.5)

    When [Column1]1 = 17, your Row 4 value will be 17 and so will the MROUND() value.

    When [Column1]1 = 17.25, both will be 17.5.

    When [Column1]1 = 17.76, both will be 18.

    When [Column1]1 = 17.74, both will be 17.5.

    BUT! When [Column1]1 = 17.75, your Row 4 value will be 17.5 while the MROUND() value will be 18.

    If you need the exact behavior of your IF expression, you can still do it in a single formula but it will be very long:

    =ROUNDDOWN([Column1]1, 0) + IF([Column1]1 - ROUNDDOWN([Column1]1, 0) < 0.25, 0, IF([Column1]1 - ROUNDDOWN([Column1]1, 0) > 0.75, 1, 0.5))

    Hope this helps,

    Dossy

Answers

  • Dossy Shiobara
    Answer ✓

    It sounds like you want MROUND() which rounds to the nearest multiple, but your specific example's boundaries defined by your IF() is slightly different than MROUND()'s behavior.

    Try:

    =MROUND([Column1]1, 0.5)

    When [Column1]1 = 17, your Row 4 value will be 17 and so will the MROUND() value.

    When [Column1]1 = 17.25, both will be 17.5.

    When [Column1]1 = 17.76, both will be 18.

    When [Column1]1 = 17.74, both will be 17.5.

    BUT! When [Column1]1 = 17.75, your Row 4 value will be 17.5 while the MROUND() value will be 18.

    If you need the exact behavior of your IF expression, you can still do it in a single formula but it will be very long:

    =ROUNDDOWN([Column1]1, 0) + IF([Column1]1 - ROUNDDOWN([Column1]1, 0) < 0.25, 0, IF([Column1]1 - ROUNDDOWN([Column1]1, 0) > 0.75, 1, 0.5))

    Hope this helps,

    Dossy

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!