Rounding ONLY Up

Dane Copple

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". 

 

My idea was this:

=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. 

J. Craig Williams

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

Dane Copple

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.

J. Craig Williams

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

Dane Copple

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!