Hello all,
Is there a way I can Round the current cell up or down depending on the content?
What I’m wanting to do is:
=1.1 → 1.0
=1.2 → 1.5
=1.6 ->1.5
=1.7 → 2.0
Is there a way to achieve this?
Cheers,
Sean.
This seemed like a tricky one so I wanted to give it a shot. There may be better ways of doing this, but it’s what I’ve come up with so far.
In a table where one column is the value entered, [Value], and the other is the result of the rounding, [Result], and both are formatted to “Number”, add this equation to the [Result] column:
If(Round(thisRow.Value - RoundDown(thisRow.Value,0),1) >= 0.7,RoundDown(thisRow.Value,0) + 1,If(Round(thisRow.Value - RoundDown(thisRow.Value,0),1) >= 0.2,RoundDown(thisRow.Value,0) + 0.5,If(Round(thisRow.Value - RoundDown(thisRow.Value,0),1) >= 0.0,RoundDown(thisRow.Value,0),"Missing Value")))
I went off what you posted here, but you can change your bounds if you need to.
This equation is taking the entered Value and then subtracting the same value using RoundDown(). It’s also using Round() to limit this to one decimal place. This leaves us with just the decimal to work with.
Then it uses the IF() statements to see what range the decimal falls into.
Lastly, it takes the RoundDown() number and adds the proper decimal value to it based on the range, so 0, 0.5, or 1.
1 Like
Hello Sean,
@benblee’s good answer got me thinking, so based on that and building slightly upon it, here is an example with controls to dictate how far in-between two numbers the RoundUp or RoundDown should occur as well as what you want to be as your basis for rounding:
Control splitPercent dictates where you want the rounding to occur
Control roundToEvery dictates where to round to
[Base] is the number you want to round
[Round Split %] is how far into rounded range the number falls
[Rounded Number] is the output
The combined calculation is as follows:
=If(round((([Base Number]-rounddown([Base Number]*(1/roundToEvery),0)/(1/roundToEvery)))/roundToEvery,2)<=splitPercent,
rounddown([Base Number]*(1/roundToEvery),0)/(1/roundToEvery),
roundup([Base Number]*(1/roundToEvery),0)/(1/roundToEvery))
Resulting in this doc: https://coda.io/d/Round-Up-or-Down_dmF6kxMGHTb/Section-1_suS2m#Rounding-Table_tumiZ
Hope that makes sense!
3 Likes
Hi @benblee and @Bobby_Ritter - I greatly appreciate the swift response of both of you and a little embarrassed that I have received such a comprehensive answer 
This has really helped me.
Thank you both.
Cheers!
1 Like
I often feel helpless when using Coda I encounter problems with basic things that I can do in 1 second in Excel. Someone will help me to round off the result of this equation? Thank you very much.
Round( premium *0.6, 0) should round it for you.
5 Likes
Awesome. It works like a charm!
I’m trying to round the following amounts like so…
10.25 → 0
99.99 → 0
100.00 → 1
534.64 → 5
1274.23 → 12
Can someone help with the formula to round? I am essentially counting the number of 100s.
Thanks in advance!
Jeff
Hey Jeff!
You can accomplish that by dividing your number and then using roundDown(). See screenshot below:
Screenshot with formulas:
Hopefully that helps!
1 Like
This got bumped and I got shocked from the earlier replies in the thread. Three-storeyed formulas, seriously? 
Maybe back in 2018 there weren’t these formulas but they have been around since I joined in early 2019:
These round the number to the nearest / up / down to the nearest multiple of multiple, respectively. So @Sean_Wood2’s challenge would be solvable as simply:
RoundTo(1.2, 0.5)
And just for the record, Round(num, places), RoundDown(num, places), and RoundUp(num, places) are the corresponding functions that round to decimal places (i.e. the second argument is not a multiple but a number of decimal places after comma)
P.S. Let’s say you didn’t have these formulas and still wanted to round to the nearest multiple. Well the formula would be this:
Round(num / multiple, 0) * multiple
Still no three-storeyed ones.
3 Likes