[libreoffice-users] Re: Missing function: Bankers Rounding
If you don't want any errors even in the 10th digit, I think it's probably a good idea to use ROUNDUP and ROUNDDOWN instead of adding or subtracting 0.005 for the final result. So, my final function for a Bankers Round that I'm using is: =IF(ROUND(100*A1-INT(100*A1),8)=0.5,IF(MOD(ROUND(100*A1-0.5,0),2),ROUNDUP(A1,2),ROUNDDOWN(A1,2)),ROUND(A1,2)) Kind of repetitious with all the ROUND functions, but works and gives an exact match to GnuCash results witch was what I needed. Usually I'm inserting something like A1/4 instead of just A1 where the banker's round of A1/4 is "my personal expenses" and the difference of that and A1 is my business expense. Without any rounding, all the data seemed to match between GnuCash and LibreOffice Calc, but the totals would be off by a few pennies - not acceptable because often a mismatch like that points to an oversight on my part in entering values into Calc. To my surprise though, simple rounding to 2 digits didn't work either. Eventually, I realized GnuCash was using this bankers rounding method. Does anyone know how to create a user-defined function in LibreOffice Calc so I could just enter "BANKROUND(A1)" instead of the messy function listed above? -- View this message in context: http://nabble.documentfoundation.org/Missing-function-Bankers-Rounding-tp2530641p2669358.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
[libreoffice-users] Re: Missing function: Bankers Rounding
plino wrote: > > I had never heard of bankers rounding before. Interesting concept. > > There is no such function in OOo/LO, Excel or Gnumeric... > > But you can easily create a function > > =IF(A1-INT(A1)=0.5;IF(ISEVEN(INT(A1));A1-0.5;A1+0.5);ROUND(A1)) > > (Adjust if your separator is a comma instead of a semi-colon) > > What this does is check if the fractional part is 0.5 and adds 0.5 to the > number if the integer is odd and subtracts if it is even. If it is not > 0.5 then it uses the regular Round() function ;) > > Hope this helps! > Due to shortcomings in LibreOffice Calc, I had to adjust your formula as follows: =IF(ROUND(A1-INT(A1),8)=0.5,IF(MOD(A1-0.5,2),A1+0.5,A1-0.5),ROUND(A1)) and for the more usual case of needing to do a Bankers Round to a penny and not a dollar, I am actually using this: =IF(ROUND(100*A1-INT(100*A1),8)=0.5,IF(MOD(100*A1-0.5,2),A1+0.005,A1-0.005),ROUND(A1,2)) The reason I needed to use ROUND... in the test is that LibraOffice Calc seems to often come up with numbers like .4... when evaluating A1-INT(A1) instead of .5, causing the test to fail when it should work. The reason I needed to use MOD... instead of ISEVEN(INT... is that after closing and opening the file, anywhere Calc needed to evaluate IF(...IF(ISEVEN(INT... it gave a "#MACRO?" error. Maybe LibraOffice can't handle nesting functions that far. By using MOD, it doesn't nest as far. Also, MOD(x,2) returns "0" for even and "1" for odd, so it's really replacing ISODD..., and so I had to add .5 instead of subtract .5 when MOD... is "true". And of course, the reason for multiplying by 100 is to use the same idea for pennies instead of dollars. -- View this message in context: http://nabble.documentfoundation.org/Missing-function-Bankers-Rounding-tp2530641p2667012.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
[libreoffice-users] Re: Missing function: Bankers Rounding
plino wrote: > > I had never heard of bankers rounding before. Interesting concept. > > There is no such function in OOo/LO, Excel or Gnumeric... > > But you can easily create a function > > =IF(A1-INT(A1)=0.5;IF(ISEVEN(INT(A1));A1-0.5;A1+0.5);ROUND(A1)) > > (Adjust if your separator is a comma instead of a semi-colon) > > What this does is check if the fractional part is 0.5 and adds 0.5 to the > number if the integer is odd and subtracts if it is even. If it is not > 0.5 then it uses the regular Round() function ;) > > Hope this helps! > Due to shortcomings in LibreOffice Calc (maybe in other spreadsheets too, I don't know), I had to adjust your formula as follows: =IF(ROUND(A1-INT(A1),8)=0.5,IF(MOD(ROUND(A1-0.5),2),A1+0.5,A1-0.5),ROUND(A1)) and for the more usual case of needing to do a Bankers Round to a penny and not a dollar, I am actually using this: =IF(ROUND(100*A1-INT(100*A1),8)=0.5,IF(MOD(ROUND(100*A1-0.5),2),A1+0.005,A1-0.005),ROUND(A1,2)) The reason I needed to use ROUND... in both tests is that LibraOffice Calc seems to often come up with numbers like .4... when evaluating A1-INT(A1) instead of .5, causing the test to fail when it should work. The reason I needed to use MOD(ROUND... instead of ISEVEN(INT... is that after closing and opening the file, anywhere Calc needed to evaluate IF(...IF(ISEVEN(INT... it gave a "#MACRO?" error. I don't know why MOD(ROUND... works fine but ISEVEN(INT doesn't. Also, MOD(x,2) returns "0" for even and "1" for odd, so it's really replacing ISODD..., and so I had to add .5 instead of subtract .5 when MOD... is "true". And of course, the reason for multiplying by 100 is to use the same idea for pennies instead of dollars. -- View this message in context: http://nabble.documentfoundation.org/Missing-function-Bankers-Rounding-tp2530641p2667339.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
[libreoffice-users] Re: Missing function: Bankers Rounding
colinkeenan wrote: > > > plino wrote: >> >> I had never heard of bankers rounding before. Interesting concept. >> >> There is no such function in OOo/LO, Excel or Gnumeric... >> >> But you can easily create a function >> >> =IF(A1-INT(A1)=0.5;IF(ISEVEN(INT(A1));A1-0.5;A1+0.5);ROUND(A1)) >> >> (Adjust if your separator is a comma instead of a semi-colon) >> >> What this does is check if the fractional part is 0.5 and adds 0.5 to the >> number if the integer is odd and subtracts if it is even. If it is not >> 0.5 then it uses the regular Round() function ;) >> >> Hope this helps! >> > > Due to shortcomings in LibreOffice Calc, I had to adjust your formula as > follows: > > =IF(ROUND(A1-INT(A1),8)=0.5,IF(MOD(A1-0.5,2),A1+0.5,A1-0.5),ROUND(A1)) > > and for the more usual case of needing to do a Bankers Round to a penny > and not a dollar, I am actually using this: > > =IF(ROUND(100*A1-INT(100*A1),8)=0.5,IF(MOD(100*A1-0.5,2),A1+0.005,A1-0.005),ROUND(A1,2)) > > The reason I needed to use ROUND... in the test is that LibraOffice Calc > seems to often come up with numbers like .4... when evaluating > A1-INT(A1) instead of .5, causing the test to fail when it should work. > > The reason I needed to use MOD... instead of ISEVEN(INT... is that after > closing and opening the file, anywhere Calc needed to evaluate > IF(...IF(ISEVEN(INT... it gave a "#MACRO?" error. Maybe LibraOffice can't > handle nesting functions that far. By using MOD, it doesn't nest as far. > Also, MOD(x,2) returns "0" for even and "1" for odd, so it's really > replacing ISODD..., and so I had to add .5 instead of subtract .5 when > MOD... is "true". > > And of course, the reason for multiplying by 100 is to use the same idea > for pennies instead of dollars. > Just now, I realized MOD(A1-.5,2) wasn't always acting right either due to LibreOffice Calc not getting the exact result on subtraction. Changing it to MOD(ROUND(A1-.5),2) fixes the problem. I don't know why MOD(ROUND... works when ISEVEN(INT... cause the "#MACRO?" error. They both use the same level of nested functions. -- View this message in context: http://nabble.documentfoundation.org/Missing-function-Bankers-Rounding-tp2530641p2667055.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
[libreoffice-users] Re: Missing function: Bankers Rounding
Hi Regina, Am 19.02.2011 15:16, Regina Henschel wrote: I think, MROUND will work for Robert. Examples: Round to nearest even Cent: MROUND(12.354;0.02) results in 12.36 But the bankster rounds to 12.35 MROUND(-12.328;0.02) results in -12.32 But the bankster rounds to -12.33 Round-to-even to one decimal place MROUND(3.49;0.2) results in 3.4 But the bankster rounds to 3.49 MROUND(2.51;0.2) results in 2.6 But the bankster rounds to 2.51 With "normal" rounding, 1/1000 digits ... 1,2,3,4 round down to previous zero 5,6,7,8,9 round up to next zero This means that there are 20% more ups than downs because of the 5 which is exactly in the middle but rounds up by mere convention. The oh-so-clever ones round up or down depending on the second last digit (1/100) being even or odd when the 1/1000 digit is 5. So the 5 goes up in 50% of all cases and down in the other 50%. 2.515 => 2.51 [5/1000 down] 2.525 => 2.53 [5/1000 up] This can be solved by some carefully designed script or sheet formula but I don't feel penuriously enough to do so. http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.misc/2004-04/5994.html gives strange results or I do not understand it. It is generic enough to be run by this interpreter. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
Re: [libreoffice-users] Re: Missing function: Bankers Rounding
Gordon Burgess-Parker wrote on 2011-02-19 12:22: > All you need to do is to set the cell format to be 2 decimal places. If > you then enter 123.455 into a cell that will round up to 123.46 > automatically for youno need for any sort of complicated formulae This is only the display format. The intern value stay by 123.455. That mean if you add more cells, then it compute always with the not rounded value! --- Have a nice day. Joachim (Germany) -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
Re: [libreoffice-users] Re: Missing function: Bankers Rounding
Hi all, Andreas Säger schrieb: Am 19.02.2011 00:50, Robert Prins wrote: Why is there no standard function to do bankers rounding (aka round-to-even)? http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_MROUND_function Indeed, MROUND is not a standard function. It is part of the (always installed) Analysis add-on. I think, MROUND will work for Robert. Examples: Round to nearest even Cent: MROUND(12.354;0.02) results in 12.36 MROUND(-12.328;0.02) results in -12.32 Round-to-even to one decimal place MROUND(3.49;0.2) results in 3.4 MROUND(2.51;0.2) results in 2.6 Kind regards Regina -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
Re: [libreoffice-users] Re: Missing function: Bankers Rounding
On 19/02/11 00:38, Robert Prins wrote: On Sat, Feb 19, 2011 at 00:24, plino wrote: I had never heard of bankers rounding before. Interesting concept. There is no such function in OOo/LO, Excel or Gnumeric... But you can easily create a function =IF(A1-INT(A1)=0.5;IF(ISEVEN(INT(A1));A1-0.5;A1+0.5);ROUND(A1)) (Adjust if your separator is a comma instead of a semi-colon) What this does is check if the fractional part is 0.5 and adds 0.5 to the number if the integer is odd and subtracts if it is even. If it is not 0.5 then it uses the regular Round() function ;) Hope this helps! Yes, but mostly no... The above works for 22.5 and -1234.5, but now I want to deal with currencies... Like $ 123.455 or € 99.125, which should be rounded to $ 123.46 and € 99.12 In other words, the issue is slightly more complicated... Robert All you need to do is to set the cell format to be 2 decimal places. If you then enter 123.455 into a cell that will round up to 123.46 automatically for youno need for any sort of complicated formulae -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
[libreoffice-users] Re: Missing function: Bankers Rounding
What I meant was: if the original numbers have a mixed number of decimal cases you can't apply my solution because of the base 2 calculations. But I assume that in a table you don't have values with 3 decimal cases mixed with one decimal case? Therefore you could go from 4 decimal cases to 1 in 3 steps using my method... In any case, the short answer is: there isn't such a round function in any of the 3 spreadsheets. Apparently the round() function in Visual Basic for Applications (VBA) does a bankers' rounding. http://groups.google.com/group/microsoft.public.excel.charting/msg/107fce6145b70d69?pli=1 Hope this helps -- View this message in context: http://nabble.documentfoundation.org/Missing-function-Bankers-Rounding-tp2530641p2533054.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
Re: [libreoffice-users] Re: Missing function: Bankers Rounding
On Sat, Feb 19, 2011 at 01:05, plino wrote: > > It's only slightly more complicated :) > > =IF(VALUE(RIGHT(A9))=5;IF(ISEVEN(VALUE(LEFT(RIGHT(A9;2;A9-0.005;A9+0.005);ROUND(A9;2)) > > Check if the last digit is 5. If it is add 0.005 if the previous digit is > odd or subtract if it is even. Otherwise use the standard round function > with two cases. > > Now, if the numbers of decimal cases is not constant, then it would be > complicated :) Actually, that is exactly what I am looking for, a round function that allows me to do bankers rounding on *any* decimal position I choose. I have files where I need to round-to-even to one decimal place... Robert -- Robert AH Prins robert.ah.pr...@gmail.com -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
[libreoffice-users] Re: Missing function: Bankers Rounding
Am 19.02.2011 02:25, Andreas Säger wrote: Am 19.02.2011 00:50, Robert Prins wrote: Why is there no standard function to do bankers rounding (aka round-to-even)? http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_MROUND_function Indeed, MROUND is not a standard function. It is part of the (always installed) Analysis add-on. SORRY, forget my MROUND suggestion. It has nothing to do with banker's rounding. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
Re: [libreoffice-users] Re: Missing function: Bankers Rounding
On 18 February 2011 18:24, plino wrote: > I had never heard of bankers rounding before. Interesting concept. I never heard it called that until recently, but was taught it many years ago. I prefer it because it is more accurate. But it requires more intelligence than most apps have. :-) > There is no such function in OOo/LO, Excel or Gnumeric... Yep. -- T. R. Valentine Your friends will argue with you. Your enemies don't care. 'When I get a little money I buy books; and if any is left I buy food and clothes.' -- Erasmus -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
[libreoffice-users] Re: Missing function: Bankers Rounding
Am 19.02.2011 00:50, Robert Prins wrote: Why is there no standard function to do bankers rounding (aka round-to-even)? http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_MROUND_function Indeed, MROUND is not a standard function. It is part of the (always installed) Analysis add-on. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
[libreoffice-users] Re: Missing function: Bankers Rounding
It's only slightly more complicated :) =IF(VALUE(RIGHT(A9))=5;IF(ISEVEN(VALUE(LEFT(RIGHT(A9;2;A9-0.005;A9+0.005);ROUND(A9;2)) Check if the last digit is 5. If it is add 0.005 if the previous digit is odd or subtract if it is even. Otherwise use the standard round function with two cases. Now, if the numbers of decimal cases is not constant, then it would be complicated :) -- View this message in context: http://nabble.documentfoundation.org/Missing-function-Bankers-Rounding-tp2530641p2530915.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
Re: [libreoffice-users] Re: Missing function: Bankers Rounding
On Sat, Feb 19, 2011 at 00:24, plino wrote: > > I had never heard of bankers rounding before. Interesting concept. > > There is no such function in OOo/LO, Excel or Gnumeric... > > But you can easily create a function > > =IF(A1-INT(A1)=0.5;IF(ISEVEN(INT(A1));A1-0.5;A1+0.5);ROUND(A1)) > > (Adjust if your separator is a comma instead of a semi-colon) > > What this does is check if the fractional part is 0.5 and adds 0.5 to the > number if the integer is odd and subtracts if it is even. If it is not 0.5 > then it uses the regular Round() function ;) > > Hope this helps! Yes, but mostly no... The above works for 22.5 and -1234.5, but now I want to deal with currencies... Like $ 123.455 or € 99.125, which should be rounded to $ 123.46 and € 99.12 In other words, the issue is slightly more complicated... Robert -- Robert AH Prins robert.ah.pr...@gmail.com -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
[libreoffice-users] Re: Missing function: Bankers Rounding
I had never heard of bankers rounding before. Interesting concept. There is no such function in OOo/LO, Excel or Gnumeric... But you can easily create a function =IF(A1-INT(A1)=0.5;IF(ISEVEN(INT(A1));A1-0.5;A1+0.5);ROUND(A1)) (Adjust if your separator is a comma instead of a semi-colon) What this does is check if the fractional part is 0.5 and adds 0.5 to the number if the integer is odd and subtracts if it is even. If it is not 0.5 then it uses the regular Round() function ;) Hope this helps! -- View this message in context: http://nabble.documentfoundation.org/Missing-function-Bankers-Rounding-tp2530641p2530764.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
