# 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 | SCS CLOUD - Smartsheet Partner

joe.goetschel@scscloud.com

Save My Contact

"The only real limitation of Smartsheet is the level of effort required to achieve your goal."

Tags:

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

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!