how to do "Round half to even"

[dobrzanski]

Hi,
As in title, how can I perform "Round half to even" ? So for example
22.5 is rounden to 22 but 23.5 is rounded to 24?
thanks

 

[Bob Phillips]

Very simplistically

=IF(MOD(A20,1)=0.5,IF(ISODD(INT(A20)),ROUNDUP(A20,0),ROUNDDOWN(A20,0)),ROUND(A20,0))

 

[strawberry]

Very simplistically

=IF(MOD(A20,1)=0.5,IF(ISODD(INT(A20)),ROUNDUP(A20,0),ROUNDDOWN(A20,0)),ROUND(A20,0))

--

HTH

Bob

Simplisitc, perhaps, but not exaclty simple:

=ROUND(A20/2,0)*2

 

[Joe User]

Simplisitc, perhaps, but not exaclty simple:
=ROUND(A20/2,0)*2

But yours is simply not exactly what the OP asked for, namely "banker's
rounding". Test with A20 equal to 22.5.

If a UDF is acceptable, try:

Function vbRound(x As Double, p As Long) As Double
vbRound = Round(x, p)
End Function


----- original message -----

Very simplistically

=IF(MOD(A20,1)=0.5,IF(ISODD(INT(A20)),ROUNDUP(A20,0),ROUNDDOWN(A20,0)),ROUND(A20,0))

--

HTH

Bob

Simplisitc, perhaps, but not exaclty simple:

=ROUND(A20/2,0)*2

 

[strawberry]

But yours is simply not exactly what the OP asked for, namely "banker's rounding"

Sorry, I don't understand your point.

 

[Joe User]

Sorry, I don't understand your point.

Probably due to a typo. I should have written: "test with 22.6".

The OP (Dobrzanski) asked for "round half to even", aka "banker's rounding".
The rule of rounding to even applies only when rounding numbers ending in 5
to the position immediately before the 5. See
http://en.wikipedia.org/wiki/Rounding for details.

Although 22.5 should round to 22, 22.6 should round to 23. With your
formula, ROUND(A20/2,0)*2, 22.6 rounds to 22.


----- original message -----

But yours is simply not exactly what the OP asked for, namely "banker's
rounding"

Sorry, I don't understand your point.

 

[Bernd P]

Hello Bob,

...
=IF(MOD(A20,1)=0.5,IF(ISODD(INT(A20)),ROUNDUP(A20,0),ROUNDDOWN(A20,0)),ROUND(A20,0))
...

Does not work for negative numbers.

This one does:
=ROUND(A1,0)+ISODD(ROUND(A1,0))*SIGN(A1-ROUND(A1,0))*(A1=INT(A1*2)/2)

Regards,
Bernd

 

[strawberry]

Probably due to a typo.  I should have written:  "test with 22.6".

The OP (Dobrzanski) asked for "round half to even", aka "banker's rounding".
The rule of rounding to even applies only when rounding numbers ending in5
to the position immediately before the 5.  Seehttp://en.wikipedia.org/wiki/Roundingfor details.

Although 22.5 should round to 22, 22.6 should round to 23.  With your
formula, ROUND(A20/2,0)*2, 22.6 rounds to 22.

OK, that makes more sense. However, while your interpretation of the
OP's intention may well be right, based upon what's actually being
requested I can't really see that it has more validity than my own.

 

[Joe User]

I can't really see that it has more validity than my own.

Try reading the subject of this thread, then read the wiki article I
referenced for the terminology.


----- original message -----

Probably due to a typo. I should have written: "test with 22.6".

The OP (Dobrzanski) asked for "round half to even", aka "banker's
rounding".
The rule of rounding to even applies only when rounding numbers ending in
5
to the position immediately before the 5.
Seehttp://en.wikipedia.org/wiki/Roundingfor details.

Although 22.5 should round to 22, 22.6 should round to 23. With your
formula, ROUND(A20/2,0)*2, 22.6 rounds to 22.

OK, that makes more sense. However, while your interpretation of the
OP's intention may well be right, based upon what's actually being
requested I can't really see that it has more validity than my own.

 

[Bob Phillips]

Well, it was certainly my interpretation as well, which is why I added the
test for a remainder of 0.5.

--

HTH

Bob

Probably due to a typo. I should have written: "test with 22.6".

The OP (Dobrzanski) asked for "round half to even", aka "banker's
rounding".
The rule of rounding to even applies only when rounding numbers ending in
5
to the position immediately before the 5.
Seehttp://en.wikipedia.org/wiki/Roundingfor details.

Although 22.5 should round to 22, 22.6 should round to 23. With your
formula, ROUND(A20/2,0)*2, 22.6 rounds to 22.

OK, that makes more sense. However, while your interpretation of the
OP's intention may well be right, based upon what's actually being
requested I can't really see that it has more validity than my own.

 

[strawberry]

The OP (Dobrzanski) asked for "round half to even", aka "banker's
FWIW, my formula entirely accords with the explanation presented at
the link provided, although I'm prepared to concede to the possibility
that that might have more to do with the way the explanation is
written than its intended interpretation!

Because it makes the thread harder to follow
Why
And guys, please stop top-posting

 

[Bob Phillips]

Why would we stop top-posting, bottom-posting is inane (and don't wheel out
that tired old argument about reading back to front, learn to adapt).