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?
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.
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