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?

[Jerry W. Lewis]

=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/micro...7fce6145b70d69

Jerry

"Andreas HÃ¥kansson" wrote:

> 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?
>
>
>

[Martin]

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

"Andreas HÃ¥kansson" wrote:

> 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?
>
>
>

[Martin]

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

"Martin" wrote:

> You need CEILING:
> =CEILING(9700000,1000000) rounds up to nearest million
>
> "Andreas HÃ¥kansson" wrote:
>
> > 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?
> >
> >
> >

[Andreas Håkansson]

Hehe, actually my last examples round down

"Martin" <Martin@discussions.microsoft.com> skrev i meddelandet
news:33FF3E8C-4B58-4328-96DF-BA5B8D601EA8@microsoft.com...
> Please use Jerry's solution - mine always rounds up (I just saw your last
> example).
>
> "Martin" wrote:
>
>> You need CEILING:
>> =CEILING(9700000,1000000) rounds up to nearest million
>>
>> "Andreas Håkansson" wrote:
>>
>> > 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?
>> >
>> >
>> >

[Andreas Håkansson]

Thanks! =)

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> skrev i meddelandet
news:7C081729-F537-4187-801D-3A07235BA33C@microsoft.com...
> =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/micro...7fce6145b70d69
>
> Jerry
>
> "Andreas Håkansson" wrote:
>
>> 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?
>>
>>
>>