Need help implementing EPA rounding method
Your formula rounds in the wrong direction if A1<0.
Also it may not give the intended result if A1 is calculated. There are 14
distinct binary numbers that all display as decimal 0.5, but your formula
will only recognize one of them as 0.5. As in my VBA function, you can treat
as equal to .5 any number that equals .5 to 15 figures by converting to a
string and back again. Thus
=ROUND(A1,0) - AND(A1-INT(VALUE(A1&""))=0.5, ISEVEN(A1))*SIGN(A1)
Should handle both issues.
Jerry
" wrote:
PapaDos wrote:
=ROUND( a1, 0 ) - AND( MOD( a1, 1 ) = 0.5, ISEVEN( a1 ) )
That was my answer. But to avoid the anomalous failure of MOD() with
large numbers, try:
=round(a1,0) - and(a1-int(a1)=0.5, iseven(a1))
|