How to Round to nearest 0.04 or 0.09

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.

Kelly86495 

How about

=MROUND(A1+0.01, 0.05)-0.01

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