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
Best Answer

It sounds like you want
MROUND()
which rounds to the nearest multiple, but your specific example's boundaries defined by yourIF()
is slightly different thanMROUND()
's behavior.Try:
=MROUND([Column1]1, 0.5)
When
[Column1]1 = 17
, your Row 4 value will be17
and so will theMROUND()
value.When
[Column1]1 = 17.25
, both will be17.5
.When
[Column1]1 = 17.76
, both will be18
.When
[Column1]1 = 17.74
, both will be17.5
.BUT! When
[Column1]1 = 17.75
, your Row 4 value will be17.5
while theMROUND()
value will be18
.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

It sounds like you want
MROUND()
which rounds to the nearest multiple, but your specific example's boundaries defined by yourIF()
is slightly different thanMROUND()
's behavior.Try:
=MROUND([Column1]1, 0.5)
When
[Column1]1 = 17
, your Row 4 value will be17
and so will theMROUND()
value.When
[Column1]1 = 17.25
, both will be17.5
.When
[Column1]1 = 17.76
, both will be18
.When
[Column1]1 = 17.74
, both will be17.5
.BUT! When
[Column1]1 = 17.75
, your Row 4 value will be17.5
while theMROUND()
value will be18
.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
Categories
Check out the Formula Handbook template!