Round Whole Dollars Up to Five Dollars

TerrieVent

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!

 

p_bureau

Hi !

 

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

 

Does this help?

 

Paul.

TerrieVent

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

p_bureau

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.

TerrieVent

Hello Paul,

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

Terrie

Rebecca Ciastko

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

Genevieve P.

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