Apr 18 2023 08:01 PM
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?
Apr 18 2023 08:49 PM
Apr 18 2023 09:22 PM
Apr 18 2023 09:36 PM
Apr 18 2023 11:25 PM
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.
Apr 19 2023 01:25 AM
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
Apr 19 2023 04:25 AM
Jul 31 2024 09:35 AM
Jul 31 2024 12:33 PM
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)