software

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?
=ROUND(n,-6) will round to the nearest million by the method where 5 always
rounds up.

What Microsoft calls quot;Banker's Roundingquot; (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 quot;unbiased roundingquot;

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
groups.google.com/group/micro...7fce6145b70d69

Jerry

quot;Andreas HÃ¥kanssonquot; wrote:

gt; Hi
gt;
gt; I want to use (I think it's called) bankers rounding on a number. What I
gt; want to do is round to the nearest whole million. So if I have
gt;
gt; 9 700 000 then it should be 10 000 000
gt; 1 500 000 then it should be 2 000 000
gt; 3 499 000 then it should be 3 000 000
gt;
gt; if there any function in excel to do this?
gt;
gt;
gt;

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

quot;Andreas HÃ¥kanssonquot; wrote:

gt; Hi
gt;
gt; I want to use (I think it's called) bankers rounding on a number. What I
gt; want to do is round to the nearest whole million. So if I have
gt;
gt; 9 700 000 then it should be 10 000 000
gt; 1 500 000 then it should be 2 000 000
gt; 3 499 000 then it should be 3 000 000
gt;
gt; if there any function in excel to do this?
gt;
gt;
gt;

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

quot;Martinquot; wrote:

gt; You need CEILING:
gt; =CEILING(9700000,1000000) rounds up to nearest million
gt;
gt; quot;Andreas HÃ¥kanssonquot; wrote:
gt;
gt; gt; Hi
gt; gt;
gt; gt; I want to use (I think it's called) bankers rounding on a number. What I
gt; gt; want to do is round to the nearest whole million. So if I have
gt; gt;
gt; gt; 9 700 000 then it should be 10 000 000
gt; gt; 1 500 000 then it should be 2 000 000
gt; gt; 3 499 000 then it should be 3 000 000
gt; gt;
gt; gt; if there any function in excel to do this?
gt; gt;
gt; gt;
gt; gt;

Hehe, actually my last examples round down

quot;Martinquot; gt; skrev i meddelandet
...
gt; Please use Jerry's solution - mine always rounds up (I just saw your last
gt; example).
gt;
gt; quot;Martinquot; wrote:
gt;
gt;gt; You need CEILING:
gt;gt; =CEILING(9700000,1000000) rounds up to nearest million
gt;gt;
gt;gt; quot;Andreas Håkanssonquot; wrote:
gt;gt;
gt;gt; gt; Hi
gt;gt; gt;
gt;gt; gt; I want to use (I think it's called) bankers rounding on a number. What
gt;gt; gt; I
gt;gt; gt; want to do is round to the nearest whole million. So if I have
gt;gt; gt;
gt;gt; gt; 9 700 000 then it should be 10 000 000
gt;gt; gt; 1 500 000 then it should be 2 000 000
gt;gt; gt; 3 499 000 then it should be 3 000 000
gt;gt; gt;
gt;gt; gt; if there any function in excel to do this?
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
Thanks! =)

quot;Jerry W. Lewisquot; gt; skrev i meddelandet
...
gt; =ROUND(n,-6) will round to the nearest million by the method where 5
gt; always
gt; rounds up.
gt;
gt; What Microsoft calls quot;Banker's Roundingquot; (for reasons that are unclear to
gt; me, since I have seen no evidence that bankers have ever used it) is the
gt; rounding method specified by the ASTM standard, that rounds to the nearest
gt; rounded number, with ties (exactly 5) resolved by rounding either up or
gt; down
gt; to produce an even final rounded digit. For example, 2500000 would round
gt; down to 2000000. Since this tends to balance the number of times that you
gt; round up vs. down, it is sometimes called quot;unbiased roundingquot;
gt;
gt; The workseet round function does not do Banker's Rounding. The VBA round
gt; function incompletely implements Banker's Rounding (it does not accept a
gt; negative number of decimal places to round to; are needed to round to the
gt; nearest million). I gave a user defined function written in VBA which
gt; fully
gt; implements Banker's Rounding at
gt; groups.google.com/group/micro...7fce6145b70d69
gt;
gt; Jerry
gt;
gt; quot;Andreas Håkanssonquot; wrote:
gt;
gt;gt; Hi
gt;gt;
gt;gt; I want to use (I think it's called) bankers rounding on a number. What I
gt;gt; want to do is round to the nearest whole million. So if I have
gt;gt;
gt;gt; 9 700 000 then it should be 10 000 000
gt;gt; 1 500 000 then it should be 2 000 000
gt;gt; 3 499 000 then it should be 3 000 000
gt;gt;
gt;gt; if there any function in excel to do this?
gt;gt;
gt;gt;
gt;gt;