How to round to nearest 5.99 or 9.99

[Colette_Rennier]

I’ve got a column that automatically generates our suggested retail price (based on labor, materials, etc.). I want to add a column that rounds that number (up or down) to the nearest 5.99 or 9.99.

So if it calculates the suggested retail is $10.25, I want there to be a second column that says $9.99. If it suggests $14.87, the other column says $15.99 etc.

[JonathanBowen]

Hi @Colette_Rennier - $4.99/$9.99 would be easier as it is a series of $5 increments, but $5.99 and $9.99 can be done:

The easy version is:

(ROUND(value/5, 0) * 5) -0.01 (format as currency)

The more complicated $5.99/$9.99 formula is:

IF(
  MOD(ROUND(value/5, 0), 2) > 0, 
  (ROUND(value/5, 0) * 5) + 0.99, 
  (ROUND(value/5, 0) * 5) - 0.01
)

JB

[Colette_Rennier]

Worked like a charm. Thank you!