Round Whole Dollars Up to Five Dollars

03/25/18 Edited 12/09/19

I have a column that takes a rate and decreases it by a discount percent:

=[List Rate]@row * (1 - ([Client Discount]@row + [Selected Discount]@row))

For Example

$ 300 less 15% discount = $ 255

$ 250 less 15% discount = $ 212.50

$ 90 less 15% discount = $ 76.50

I want to apply calculation that will result in rounding up to the nearest $5

For Example:

$ 300 less 15% discount = $ 255

$ 250 less 15% discount = $ 215 (instead of current value of $ 212.50)

$ 90 less 15% discount = $ 80 (instead of current value of $ 76.50)

Would appreciate any recommendations!

 

Popular Tags:

Comments

  • Hi !

     

    You could use something like "=ROUNDUP(Start_Value / 5, 0) * 5"

     

    Does this help?

     

    Paul.

  • edited 03/26/18

    Hello Paul,

     

    Thank you for your quick reply.  When I apply the recommended formula, it is rounding up and down.  I need the number to only round "up" to the next $5.

    I am unable to see a "roundup" function in Smartsheet, I only find "round".  I am starting with an equal sign, should there be a different approach?

     

    Terrie

  • Hi,

     

    sorry, there is not ROUNDUP formula in Smartsheet !

     

    To achieve a ROUNDUP, you can use ROUND(Your_Value+0.49,0).

     

    So in your case : "=ROUND(Start_Value / 5 + 0.49, 0) * 5"

     

    Hope this helps!

     

    Paul.

  • Hello Paul,

    That did it perfectly.  Thank you so much!!!

    Terrie

  • I have a follow up question to this- how would I incorporate this function into a cell with a formula already in it?

    I have sum of several values, and I want to round it to the nearest hundred

  • Hi @Rebecca Ciastko

    Have the ROUND function around your formula, like how an IFERROR function works.


    = ROUND(FORMULA(), decimal number)


    Keep in mind that ROUND works for decimals. If you're looking to round to a different value, you may want to look at either the FLOOR function or the CEILING function.

    If you need help with this, it would be useful to see your current formula (copy/paste in the response).

    Thanks!

    Genevieve

Sign In or Register to comment.