Forum Discussion
rules for rounding
Hi, My name is tom and I'm searching for a way to alter the traditional rules for rounding numers.
Normaly when using decimals 0,0 to 0,4 becomes 0,0 and 0,5 to 0,9 becomes 1,0.
I am looking for a way to round differently.
0,0001 ; 0,0002 ; 0,0003 needs to be rounded to 0,0000
0,0004 ; 0,0005 ; 0,0006 needs to be rounded to 0,0005
0,0007 ; 0,0008 ; 0,0009 needs to be rouded to 0,0010
The 3 decimals before need to be rounded traditionaly to mathematical rules .
It is a calculation used in a belgium financial situation that I would like to apply in an excelsheet.
Is there someone who can help me out with a formula or give me a hint to help me on the way.
Thank you very much
Kind regards
tom
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?
15 Replies
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.
