Forum Discussion
rules for rounding
tombungalo It isn't pretty but this equation will work
=ROUNDDOWN(C4,3)+MAX(INT(INT(MOD(C4*10000,10)-1)/3)*5,0)/10000but that is only the special rounding you mentioned
You then said the other 3 decimal places should follow normal rules, but you don't round to multiple levels. How do you know what level to round to?
tombungalo It isn't pretty but this equation will work
=ROUNDDOWN(C4,3)+MAX(INT(INT(MOD(C4*10000,10)-1)/3)*5,0)/10000but that is only the special rounding you mentioned
You then said the other 3 decimal places should follow normal rules, but you don't round to multiple levels. How do you know what level to round to?
Hello mtarler ,
Thank you very much for this incredible formula. I translated it to work in my dutch excelprogram and I just had to change the )-1)/3)*5 into )-1)/4)*5 to get the rounding I was searching for but encountered an extra difficulty.
for instance
I need to have 413,3423 to be rounded to 413,3420
413,3424 to 413,3425
and 413,3428 to 413,3430
other example 413,8893 to be rounded to 413,8890 (1,2,3 goes to 0)
413,8894 to 413,8895 (4,5,6 goes to 5)
and 413,8898 to 413,8900 (7,8,9 goes to 10)
413,9998 to 414,0000
These are just random numbers but it might give you an idea on what I'm looking for.
I don't know if this is possible but it would be super helpfull to make calculations. Now I have to manually make al the roundings in different databases.
Thank again for the help thusfar
Kind regards
tom
tombungaloI don't understand. It is or isn't working and if not why? can you give specific examples with what the formula outputs vs what should be output? I also don't understand why you:
- had to change the )-1)/3)*5 into )-1)/4)*5
the point of that -1)/3 is because 3/3 =>1 and 6/3 =>2 but you need 3 and 6 to round down so -1 shifts the scale so 1,2,3 =>0 4,5,6 =>1 and 7,8,9 =>2 and then *5 makes them =>0 , 5, 10
the "MAX" gets rid of the -5 that happens when you start with a 0
so changing to /4 makes no sense to me. if the original /3 isn't working right you'll have to let me know why/how and I can suggest a different tweak.
Normally the rounding would be
= MROUND(+values, epsilon)
where epsilon = 0.0005. To get additional values rounding to the alternating, 0.0010 points, first group into 3 blocks, then round
= FLOOR(CEILING(values, 2*epsilon/3), epsilon)
