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
Dane Copple
edited 12/09/19 in Archived 2017 Posts

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. 

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    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

  • Dane Copple
    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.

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    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

  • Dane Copple
    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.