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 .499999999... 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 [email protected] List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
