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
"The only real limitation of Smartsheet is the level of effort required to achieve your goal."
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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 360 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!