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?
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)
wow, I don't think of using FLOOR and CEILING that way.
BUT, the problem is that it doesn't exactly work (if I understand the requirements correctly).
so I was going to do something similar with ROUND but the problem is when you consider additional digits.
MY understanding/assumption is that 1.1113999999999999 should round to 1.1110 and 1.111699999999 should round to 1.1115
You technique uses 1.1113333333... and 1.11166666... as the thresholds
So I think 1.11134 should round to 1.1110 but your technique goes to 1.1115
Similarly I think 1.11167 should round to 1.1115 but yours goes to 1.1120
tombungalo maybe you should check what the rule says
Thanks for the extra formula and input. It is always nice to learn new ways to combine formulas. As I'm just beginning to learn using the excel advanced formulas your help came in as a gift from the sky .
I've been checking the rules and for this problem it would have to be the formula of mtarler because of the extra digits used in the calculation as you said previously.
Enjoy your weekend and good health to you both,
Kind regards tom
Hello, I attached the excel file I'm trying to make with some examples of what I want to achieve.
I changed the number in the formula again because as you said it didn't do the trick.
Hopefully it makes everything a little clearer.
kind regards
tom
tombungalo I think your translation was mistaken on "ROUNDDOWN" and came back to me as "FLOOR" instead which doesn't act the same. Also, when you added the /3 back in you made a mistake on the ")" I corrected the formula in the attached file and it will hopefully just work/translate for you.
Yes, thank you thank you thank you . That did the trick.
You made my day and saved me a lot of future work. Now the fun part can start to form all different databases in an easy to use excelsheet.
I wish you a nice and healthy weekend ,
kind regards
tom
