This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Round the original number to 2 decimal places and extract the last digit (i.e. everything after the decimal place) keeping the integer in a different column. You'll need to convert to text to apply the Right formula and then convert back to a number.
Apply a conditional (IF THEN) formula to replace the last digit with 5, 9 or 0 based on the ranges that apply to each number.
Add the rounded remainder to the integer.
Hope this makes sense. I would have built out an example but the logic for step 3 (i.e. what range of numbers applies to 0, 5 or 9) wasn't immediately obvious to me.
Not the most elegant workflow i've ever produced, but i think it's doing the trick.
My method involves rounding to 2 decimal places, materialising the 3x rounded equivalents of the same as per the 2nd decimal digit: 0, 5 and 9 and finding the minimum one of them based on absolute difference compared to the first's operations output (rounding).
I'm sure this workflow can be further optimised (even for clarity of naming conventions) and there will be different methods to achieve the same, but i hope this can get you close to your ultimate solution.