Rounding Numbers

#1

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.

#2

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
#3

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!

2 Likes
#4

That’s a cool and flexible solution @Bobby_Ritter !

I like it!

#5

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 :slight_smile:
This has really helped me.
Thank you both.

Cheers!

1 Like
#6

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.

Image%205

#7

Round( premium *0.6, 0) should round it for you.

1 Like