#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Rounding ONLY Up

Options
edited 12/09/19

Hello,

I have a construction equipment management sheet and I am trying to create a customized rounding formula for the "months rented" cell. Equipment rentals usually charge at intervals of 1-week, after that at 2-week, then after that at 1-month. Because of this, having a value like "1.3 or 1.6 months rented" doesn't make any sense, because they will charge based on 0.25, 0.50, or 1.0 months.

What I want my formula to do is look at the cell and round up to the nearest HIGHER interval, so if cell A said "32.1", cell B would change it to "32.25," or if it said "14.3", cell B would be "14.5" or if it said "4.6" cell B would say "5".

=IF(RIGHT([Months - Not Rounded]1, 1) >= 5, [Months - Not Rounded]1 + 1 - RIGHT([Months - Not Rounded]1, 1), OTHER NESTED FUNCTIONS)

However, this formula doesn't work once the bold part is inserted, and obviously the other functions need be inserted.

Tags:

• ✭✭✭✭✭✭
Options

Dane,

What type of colum is the [Months - Not Rounded]?

Appears to be Text/Number (but formatted as text).

How is this data derived?

Craig

• Options

J. Craig Williams,

The Months - Not Rounded column is a Text/Number column type, with values rounded to the nearest tenth, for example 12.5, 3.7,  20.2.

That value is derived by  [Date Off Rent]  -  [Date Arrrived] = 10.2 Months.

The value to the nearest tenth, so I figured I could just use the RIGHT functions to make the calculation happen, but to no avail.

• ✭✭✭✭✭✭
Options

RIGHT() returns text.

Try VALUE(RIGHT()) and see if that works.

Let me know if it does not. If not, I will take a look late tonight at the earliest.

Craig

• Options

DUDE, YOU ARE THE MAN!

The value was the issue!

I ended up simplifying it to "if it's larger than two weeks (.5), that round it up. If it's lower than .5, leave the number alone.

EXPECTED MONTHS ON SITE

=IF(VALUE(RIGHT([Months - Not Rounded]9, 1)) >= 5, [Months - Not Rounded]9 + 1 - VALUE(RIGHT([Months - Not Rounded]9, 2)), [Months - Not Rounded]9)

Thank you SO much for your help, sir! This is an absolute God-send!

This discussion has been closed.