How to Round to nearest 0.04 or 0.09

[Kelly86495]

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?

[#2]

What if it is 5.57? Can you explain logic?

[#3]

@Harun24HR 

If it's 5.57 it would round up to 5.59

[#4]

Apologies its to the closest 0.05 or 0.09

[#5]

@Kelly86495 

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.

I hope this helps! …if not please provide more Information.

[#6]

@Kelly86495 

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

[#7]

@Kelly86495 

How about

=MROUND(A1+0.01, 0.05)-0.01

[#8]

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...

[#9]

@kidaelo64 

How about

=A1+CHOOSE(MOD(100*A1, 10)+1, -0.03, 0.03, 0.02, 0.01, 0, -0.01, 0.01, 0, -0.01, -0.02)