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