Rounding

[Andreas Håkansson]

Hi

I want to use (I think it's called) bankers rounding on a number. What I
want to do is round to the nearest whole million. So if I have

9 700 000 then it should be 10 000 000
1 500 000 then it should be 2 000 000
3 499 000 then it should be 3 000 000

if there any function in excel to do this?

 

[Guest]

=ROUND(n,-6) will round to the nearest million by the method where 5 always
rounds up.

What Microsoft calls "Banker's Rounding" (for reasons that are unclear to
me, since I have seen no evidence that bankers have ever used it) is the
rounding method specified by the ASTM standard, that rounds to the nearest
rounded number, with ties (exactly 5) resolved by rounding either up or down
to produce an even final rounded digit. For example, 2500000 would round
down to 2000000. Since this tends to balance the number of times that you
round up vs. down, it is sometimes called "unbiased rounding"

The workseet round function does not do Banker's Rounding. The VBA round
function incompletely implements Banker's Rounding (it does not accept a
negative number of decimal places to round to; are needed to round to the
nearest million). I gave a user defined function written in VBA which fully
implements Banker's Rounding at
http://groups.google.com/group/microsoft.public.excel.charting/msg/107fce6145b70d69

Jerry

 

[Guest]

You need CEILING:
=CEILING(9700000,1000000) rounds up to nearest million

 

[Guest]

Please use Jerry's solution - mine always rounds up (I just saw your last
example).

 

[Andreas Håkansson]

Hehe, actually my last examples round down

Please use Jerry's solution - mine always rounds up (I just saw your last
example).

 

[Andreas Håkansson]

Thanks! =)

=ROUND(n,-6) will round to the nearest million by the method where 5
always
rounds up.

What Microsoft calls "Banker's Rounding" (for reasons that are unclear to
me, since I have seen no evidence that bankers have ever used it) is the
rounding method specified by the ASTM standard, that rounds to the nearest
rounded number, with ties (exactly 5) resolved by rounding either up or
down
to produce an even final rounded digit. For example, 2500000 would round
down to 2000000. Since this tends to balance the number of times that you
round up vs. down, it is sometimes called "unbiased rounding"

The workseet round function does not do Banker's Rounding. The VBA round
function incompletely implements Banker's Rounding (it does not accept a
negative number of decimal places to round to; are needed to round to the
nearest million). I gave a user defined function written in VBA which
fully
implements Banker's Rounding at
http://groups.google.com/group/microsoft.public.excel.charting/msg/107fce6145b70d69

Jerry