# How to Round to nearest 0.04 or 0.09

Copper Contributor

# How to Round to nearest 0.04 or 0.09

Hello,

I am trying to round product prices to the nearest 0.04 or 0.09. For example if a price is 5.88 I need it rounded to 5.89, and if a product is 5.56 I need it to round to 5.54. I ahve tried various round formulas with no success.

Any suggestions?

8 Replies

# Re: How to Round to nearest 0.04 or 0.09

What if it is 5.57? Can you explain logic?

# Re: How to Round to nearest 0.04 or 0.09

If it's 5.57 it would round up to 5.59

# Re: How to Round to nearest 0.04 or 0.09

Apologies its to the closest 0.05 or 0.09

# Re: How to Round to nearest 0.04 or 0.09

You can use the MROUND function in Excel to round a number to the nearest multiple of a specified value. Here’s an example formula that rounds a number in cell A1 to the nearest 0.05: =MROUND(A1, 0.05). This will round the number to the nearest multiple of 0.05, which means it will always end in either 0.00, 0.05, 0.10, etc.

However, since you want to round to either 0.04 or 0.09 specifically, you can use an IF statement to check if the decimal part of the number is less than or equal to 0.045. If it is, you can round down to the nearest 0.04 using the FLOOR function. If it’s greater than 0.045, you can round up to the nearest 0.09 using the CEILING function.

Here’s an example formula that does this for a number in cell A1:

=IF(A1-INT(A1)<=0.045,FLOOR(A1,0.05)-0.01,CEILING(A1,0.05)+0.04)

This formula first checks if the decimal part of the number (calculated using A1-INT(A1)) is less than or equal to 0.045. If it is, it rounds down to the nearest multiple of 0.05 using FLOOR(A1,0.05) and then subtracts 0.01 to get to the nearest multiple of 0.04.

If the decimal part is greater than 0.045, it rounds up to the nearest multiple of 0.05 using CEILING(A1,0.05) and then adds 0.04 to get to the nearest multiple of 0.09.

# Re: How to Round to nearest 0.04 or 0.09

You don't say what the result should be for an entry such as 5.87, which is equidistant from 5.85 and 5.89. This will favour the former:

``````=LET(
ζ, A1,
ξ, MROUND(ζ, 0.05),
ξ - IF(MOD(ξ * 100, 10), 0, 0.01)
)``````

Regards

# Re: How to Round to nearest 0.04 or 0.09

=MROUND(A1+0.01, 0.05)-0.01

# Re: How to Round to nearest 0.04 or 0.09

I used your mround suggestion then subtracted .01 , thank you!
=mround(A1, 0.05)-.01

but now I have another client interested in having the cents end in .07 and .04 instead of .09/.04... hmm...