Function for Rounding of Number

I want to round of number for example:

1.5 = 1
1.6 = 2

What function should I use. Please help me. Thanks

=round(a1)
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"aries0070" <[email protected]> wrote
in message news:[email protected]...
>
> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks
>
>
> --
> aries0070
> ------------------------------------------------------------------------
> aries0070's Profile:
http://www.excelforum.com/member.php...o&userid=25077
> View this thread: http://www.excelforum.com/showthread...hreadid=386182
>

That will give a syntax error, since you didn't include the second
argument.

Note that even =ROUND(A1,0) will give the wrong answer for the OP's
requirements. =ROUND(1.5,0) = 2, not 1.



In article <[email protected]>,
"Anne Troy" <[email protected]> wrote:

> =round(a1)
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "aries0070" <[email protected]> wrote
> in message news:[email protected]...
> >
> > I want to round of number for example:
> >
> > 1.5 = 1
> > 1.6 = 2
> >
> > What function should I use. Please help me. Thanks
> >
> >
> > --
> > aries0070
> > ------------------------------------------------------------------------
> > aries0070's Profile:
> http://www.excelforum.com/member.php...o&userid=25077
> > View this thread: http://www.excelforum.com/showthread...hreadid=386182
> >

Thanks. I blew that one, completely. Tried to get back to it when I saw they
wanted to round DOWN on .5, and got tied up. Thanks.
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> That will give a syntax error, since you didn't include the second
> argument.
>
> Note that even =ROUND(A1,0) will give the wrong answer for the OP's
> requirements. =ROUND(1.5,0) = 2, not 1.
>
>
>
> In article <[email protected]>,
> "Anne Troy" <[email protected]> wrote:
>
> > =round(a1)
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> > www.MyExpertsOnline.com
> >
> >
> > "aries0070" <[email protected]>
wrote
> > in message
news:[email protected]...
> > >
> > > I want to round of number for example:
> > >
> > > 1.5 = 1
> > > 1.6 = 2
> > >
> > > What function should I use. Please help me. Thanks
> > >
> > >
> > > --
> > > aries0070
> >
> ------------------------------------------------------------------------
> > > aries0070's Profile:
> > http://www.excelforum.com/member.php...o&userid=25077
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=386182
> > >

Thanks to all. I got it.

One way:

=ROUND(A1,0)-(MOD(A1,1)=0.5)

In article <[email protected]>,
aries0070 <[email protected]>
wrote:

> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks

Note: that works only for non-negative numbers. If you may be rounding
negative numbers using the same pattern, use

=ROUND(A1,0)-SIGN(A1)*(MOD(A1,1)=0.5)

In article <[email protected]>,
JE McGimpsey <[email protected]> wrote:

> One way:
>
> =ROUND(A1,0)-(MOD(A1,1)=0.5)

Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.

=Round(a1,0) will behave as above. If you want to round
down at 1.5 you need to apply as

=Round(a1-1,0)


Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

Not really:

=ROUND(A1-1,0) will round 1.6 to 1, not 2.

In article <[email protected]>,
elioch <elioch.1s0oq1@> wrote:

> If you want to round down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>

Not always! Some people, when rounding a number 5 as the test digit will
round to even value.
1.35 -> 1.4
1.45 -> 1.4
I have seen this called Banker's Rounding, New Math rounding, Australian
rounding.
Best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"elioch" <elioch.1s0oq1@> wrote in message
news:[email protected]...
>
> Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.
>
> =Round(a1,0) will behave as above. If you want to round
> down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>
>
> Elioch
>
>
> --
> eliochPosted from http://www.pcreview.co.uk/ newsgroup access
>

That type of rounding is very common in science, too, where it prevents
a bias away from zero.

There are many other flavors. See

http://support.microsoft.com/default...b;en-us;196652



In article <[email protected]>,
"Bernard Liengme" <[email protected]> wrote:

> I have seen this called Banker's Rounding, New Math rounding, Australian
> rounding.

A more bullet-proof approach is given in

http://groups-beta.google.com/group/...7fce6145b70d69

As an example of the difference, try rounding =1110*0.0865 to 2 decimal
places with microsoft's bround function and my ASTMround. Microsoft's
bround will round 96.0150000000000 to 96.01, where my ASTMround will
correctly round it to 96.02. Another example of the difference is
referenced in that post.

Jerry

JE McGimpsey wrote:

> That type of rounding is very common in science, too, where it prevents
> a bias away from zero.
>
> There are many other flavors. See
>
> http://support.microsoft.com/default...b;en-us;196652
>
>
>
> In article <[email protected]>,
> "Bernard Liengme" <[email protected]> wrote:
>
>
>>I have seen this called Banker's Rounding, New Math rounding, Australian
>>rounding.

CORRECTION

=Round(a1,0) will behave as above. If you want to round down at 1.5 you
need to apply as

=Round(a1-0.1,0) it ishould be minus 0.1 and not minus 1 as stated
before.

Sorry

Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

=round(a1)
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"aries0070" <[email protected]> wrote
in message news:[email protected]...
>
> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks
>
>
> --
> aries0070
> ------------------------------------------------------------------------
> aries0070's Profile:
http://www.excelforum.com/member.php...o&userid=25077
> View this thread: http://www.excelforum.com/showthread...hreadid=386182
>

That will give a syntax error, since you didn't include the second
argument.

Note that even =ROUND(A1,0) will give the wrong answer for the OP's
requirements. =ROUND(1.5,0) = 2, not 1.



In article <[email protected]>,
"Anne Troy" <[email protected]> wrote:

> =round(a1)
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "aries0070" <[email protected]> wrote
> in message news:[email protected]...
> >
> > I want to round of number for example:
> >
> > 1.5 = 1
> > 1.6 = 2
> >
> > What function should I use. Please help me. Thanks
> >
> >
> > --
> > aries0070
> > ------------------------------------------------------------------------
> > aries0070's Profile:
> http://www.excelforum.com/member.php...o&userid=25077
> > View this thread: http://www.excelforum.com/showthread...hreadid=386182
> >

Thanks. I blew that one, completely. Tried to get back to it when I saw they
wanted to round DOWN on .5, and got tied up. Thanks.
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> That will give a syntax error, since you didn't include the second
> argument.
>
> Note that even =ROUND(A1,0) will give the wrong answer for the OP's
> requirements. =ROUND(1.5,0) = 2, not 1.
>
>
>
> In article <[email protected]>,
> "Anne Troy" <[email protected]> wrote:
>
> > =round(a1)
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> > www.MyExpertsOnline.com
> >
> >
> > "aries0070" <[email protected]>
wrote
> > in message
news:[email protected]...
> > >
> > > I want to round of number for example:
> > >
> > > 1.5 = 1
> > > 1.6 = 2
> > >
> > > What function should I use. Please help me. Thanks
> > >
> > >
> > > --
> > > aries0070
> >
> ------------------------------------------------------------------------
> > > aries0070's Profile:
> > http://www.excelforum.com/member.php...o&userid=25077
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=386182
> > >

Thanks. I blew that one, completely. Tried to get back to it when I saw they
wanted to round DOWN on .5, and got tied up. Thanks.
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> That will give a syntax error, since you didn't include the second
> argument.
>
> Note that even =ROUND(A1,0) will give the wrong answer for the OP's
> requirements. =ROUND(1.5,0) = 2, not 1.
>
>
>
> In article <[email protected]>,
> "Anne Troy" <[email protected]> wrote:
>
> > =round(a1)
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> > www.MyExpertsOnline.com
> >
> >
> > "aries0070" <[email protected]>
wrote
> > in message
news:[email protected]...
> > >
> > > I want to round of number for example:
> > >
> > > 1.5 = 1
> > > 1.6 = 2
> > >
> > > What function should I use. Please help me. Thanks
> > >
> > >
> > > --
> > > aries0070
> >
> ------------------------------------------------------------------------
> > > aries0070's Profile:
> > http://www.excelforum.com/member.php...o&userid=25077
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=386182
> > >

Thanks. I blew that one, completely. Tried to get back to it when I saw they
wanted to round DOWN on .5, and got tied up. Thanks.
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> That will give a syntax error, since you didn't include the second
> argument.
>
> Note that even =ROUND(A1,0) will give the wrong answer for the OP's
> requirements. =ROUND(1.5,0) = 2, not 1.
>
>
>
> In article <[email protected]>,
> "Anne Troy" <[email protected]> wrote:
>
> > =round(a1)
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> > www.MyExpertsOnline.com
> >
> >
> > "aries0070" <[email protected]>
wrote
> > in message
news:[email protected]...
> > >
> > > I want to round of number for example:
> > >
> > > 1.5 = 1
> > > 1.6 = 2
> > >
> > > What function should I use. Please help me. Thanks
> > >
> > >
> > > --
> > > aries0070
> >
> ------------------------------------------------------------------------
> > > aries0070's Profile:
> > http://www.excelforum.com/member.php...o&userid=25077
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=386182
> > >

That will give a syntax error, since you didn't include the second
argument.

Note that even =ROUND(A1,0) will give the wrong answer for the OP's
requirements. =ROUND(1.5,0) = 2, not 1.



In article <[email protected]>,
"Anne Troy" <[email protected]> wrote:

> =round(a1)
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "aries0070" <[email protected]> wrote
> in message news:[email protected]...
> >
> > I want to round of number for example:
> >
> > 1.5 = 1
> > 1.6 = 2
> >
> > What function should I use. Please help me. Thanks
> >
> >
> > --
> > aries0070
> > ------------------------------------------------------------------------
> > aries0070's Profile:
> http://www.excelforum.com/member.php...o&userid=25077
> > View this thread: http://www.excelforum.com/showthread...hreadid=386182
> >

That will give a syntax error, since you didn't include the second
argument.

Note that even =ROUND(A1,0) will give the wrong answer for the OP's
requirements. =ROUND(1.5,0) = 2, not 1.



In article <[email protected]>,
"Anne Troy" <[email protected]> wrote:

> =round(a1)
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "aries0070" <[email protected]> wrote
> in message news:[email protected]...
> >
> > I want to round of number for example:
> >
> > 1.5 = 1
> > 1.6 = 2
> >
> > What function should I use. Please help me. Thanks
> >
> >
> > --
> > aries0070
> > ------------------------------------------------------------------------
> > aries0070's Profile:
> http://www.excelforum.com/member.php...o&userid=25077
> > View this thread: http://www.excelforum.com/showthread...hreadid=386182
> >

=round(a1)
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"aries0070" <[email protected]> wrote
in message news:[email protected]...
>
> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks
>
>
> --
> aries0070
> ------------------------------------------------------------------------
> aries0070's Profile:
http://www.excelforum.com/member.php...o&userid=25077
> View this thread: http://www.excelforum.com/showthread...hreadid=386182
>

One way:

=ROUND(A1,0)-(MOD(A1,1)=0.5)

In article <[email protected]>,
aries0070 <[email protected]>
wrote:

> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks

One way:

=ROUND(A1,0)-(MOD(A1,1)=0.5)

In article <[email protected]>,
aries0070 <[email protected]>
wrote:

> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks

Note: that works only for non-negative numbers. If you may be rounding
negative numbers using the same pattern, use

=ROUND(A1,0)-SIGN(A1)*(MOD(A1,1)=0.5)

In article <[email protected]>,
JE McGimpsey <[email protected]> wrote:

> One way:
>
> =ROUND(A1,0)-(MOD(A1,1)=0.5)

Note: that works only for non-negative numbers. If you may be rounding
negative numbers using the same pattern, use

=ROUND(A1,0)-SIGN(A1)*(MOD(A1,1)=0.5)

In article <[email protected]>,
JE McGimpsey <[email protected]> wrote:

> One way:
>
> =ROUND(A1,0)-(MOD(A1,1)=0.5)

Note: that works only for non-negative numbers. If you may be rounding
negative numbers using the same pattern, use

=ROUND(A1,0)-SIGN(A1)*(MOD(A1,1)=0.5)

In article <[email protected]>,
JE McGimpsey <[email protected]> wrote:

> One way:
>
> =ROUND(A1,0)-(MOD(A1,1)=0.5)

Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.

=Round(a1,0) will behave as above. If you want to round
down at 1.5 you need to apply as

=Round(a1-1,0)


Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.

=Round(a1,0) will behave as above. If you want to round
down at 1.5 you need to apply as

=Round(a1-1,0)


Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

Not really:

=ROUND(A1-1,0) will round 1.6 to 1, not 2.

In article <[email protected]>,
elioch <elioch.1s0oq1@> wrote:

> If you want to round down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>

Not really:

=ROUND(A1-1,0) will round 1.6 to 1, not 2.

In article <[email protected]>,
elioch <elioch.1s0oq1@> wrote:

> If you want to round down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>

Not always! Some people, when rounding a number 5 as the test digit will
round to even value.
1.35 -> 1.4
1.45 -> 1.4
I have seen this called Banker's Rounding, New Math rounding, Australian
rounding.
Best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"elioch" <elioch.1s0oq1@> wrote in message
news:[email protected]...
>
> Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.
>
> =Round(a1,0) will behave as above. If you want to round
> down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>
>
> Elioch
>
>
> --
> eliochPosted from http://www.pcreview.co.uk/ newsgroup access
>

Not always! Some people, when rounding a number 5 as the test digit will
round to even value.
1.35 -> 1.4
1.45 -> 1.4
I have seen this called Banker's Rounding, New Math rounding, Australian
rounding.
Best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"elioch" <elioch.1s0oq1@> wrote in message
news:[email protected]...
>
> Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.
>
> =Round(a1,0) will behave as above. If you want to round
> down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>
>
> Elioch
>
>
> --
> eliochPosted from http://www.pcreview.co.uk/ newsgroup access
>

That type of rounding is very common in science, too, where it prevents
a bias away from zero.

There are many other flavors. See

http://support.microsoft.com/default...b;en-us;196652



In article <[email protected]>,
"Bernard Liengme" <[email protected]> wrote:

> I have seen this called Banker's Rounding, New Math rounding, Australian
> rounding.

That type of rounding is very common in science, too, where it prevents
a bias away from zero.

There are many other flavors. See

http://support.microsoft.com/default...b;en-us;196652



In article <[email protected]>,
"Bernard Liengme" <[email protected]> wrote:

> I have seen this called Banker's Rounding, New Math rounding, Australian
> rounding.

A more bullet-proof approach is given in

http://groups-beta.google.com/group/...7fce6145b70d69

As an example of the difference, try rounding =1110*0.0865 to 2 decimal
places with microsoft's bround function and my ASTMround. Microsoft's
bround will round 96.0150000000000 to 96.01, where my ASTMround will
correctly round it to 96.02. Another example of the difference is
referenced in that post.

Jerry

JE McGimpsey wrote:

> That type of rounding is very common in science, too, where it prevents
> a bias away from zero.
>
> There are many other flavors. See
>
> http://support.microsoft.com/default...b;en-us;196652
>
>
>
> In article <[email protected]>,
> "Bernard Liengme" <[email protected]> wrote:
>
>
>>I have seen this called Banker's Rounding, New Math rounding, Australian
>>rounding.

A more bullet-proof approach is given in

http://groups-beta.google.com/group/...7fce6145b70d69

As an example of the difference, try rounding =1110*0.0865 to 2 decimal
places with microsoft's bround function and my ASTMround. Microsoft's
bround will round 96.0150000000000 to 96.01, where my ASTMround will
correctly round it to 96.02. Another example of the difference is
referenced in that post.

Jerry

JE McGimpsey wrote:

> That type of rounding is very common in science, too, where it prevents
> a bias away from zero.
>
> There are many other flavors. See
>
> http://support.microsoft.com/default...b;en-us;196652
>
>
>
> In article <[email protected]>,
> "Bernard Liengme" <[email protected]> wrote:
>
>
>>I have seen this called Banker's Rounding, New Math rounding, Australian
>>rounding.

A more bullet-proof approach is given in

http://groups-beta.google.com/group/...7fce6145b70d69

As an example of the difference, try rounding =1110*0.0865 to 2 decimal
places with microsoft's bround function and my ASTMround. Microsoft's
bround will round 96.0150000000000 to 96.01, where my ASTMround will
correctly round it to 96.02. Another example of the difference is
referenced in that post.

Jerry

JE McGimpsey wrote:

> That type of rounding is very common in science, too, where it prevents
> a bias away from zero.
>
> There are many other flavors. See
>
> http://support.microsoft.com/default...b;en-us;196652
>
>
>
> In article <[email protected]>,
> "Bernard Liengme" <[email protected]> wrote:
>
>
>>I have seen this called Banker's Rounding, New Math rounding, Australian
>>rounding.

That type of rounding is very common in science, too, where it prevents
a bias away from zero.

There are many other flavors. See

http://support.microsoft.com/default...b;en-us;196652



In article <[email protected]>,
"Bernard Liengme" <[email protected]> wrote:

> I have seen this called Banker's Rounding, New Math rounding, Australian
> rounding.

Not really:

=ROUND(A1-1,0) will round 1.6 to 1, not 2.

In article <[email protected]>,
elioch <elioch.1s0oq1@> wrote:

> If you want to round down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>

Not always! Some people, when rounding a number 5 as the test digit will
round to even value.
1.35 -> 1.4
1.45 -> 1.4
I have seen this called Banker's Rounding, New Math rounding, Australian
rounding.
Best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"elioch" <elioch.1s0oq1@> wrote in message
news:[email protected]...
>
> Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.
>
> =Round(a1,0) will behave as above. If you want to round
> down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>
>
> Elioch
>
>
> --
> eliochPosted from http://www.pcreview.co.uk/ newsgroup access
>

CORRECTION

=Round(a1,0) will behave as above. If you want to round down at 1.5 you
need to apply as

=Round(a1-0.1,0) it ishould be minus 0.1 and not minus 1 as stated
before.

Sorry

Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

CORRECTION

=Round(a1,0) will behave as above. If you want to round down at 1.5 you
need to apply as

=Round(a1-0.1,0) it ishould be minus 0.1 and not minus 1 as stated
before.

Sorry

Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

=round(a1)
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"aries0070" <[email protected]> wrote
in message news:[email protected]...
>
> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks
>
>
> --
> aries0070
> ------------------------------------------------------------------------
> aries0070's Profile:
http://www.excelforum.com/member.php...o&userid=25077
> View this thread: http://www.excelforum.com/showthread...hreadid=386182
>

One way:

=ROUND(A1,0)-(MOD(A1,1)=0.5)

In article <[email protected]>,
aries0070 <[email protected]>
wrote:

> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks

Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.

=Round(a1,0) will behave as above. If you want to round
down at 1.5 you need to apply as

=Round(a1-1,0)


Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

CORRECTION

=Round(a1,0) will behave as above. If you want to round down at 1.5 you
need to apply as

=Round(a1-0.1,0) it ishould be minus 0.1 and not minus 1 as stated
before.

Sorry

Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

=round(a1)
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"aries0070" <[email protected]> wrote
in message news:[email protected]...
>
> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks
>
>
> --
> aries0070
> ------------------------------------------------------------------------
> aries0070's Profile:
http://www.excelforum.com/member.php...o&userid=25077
> View this thread: http://www.excelforum.com/showthread...hreadid=386182
>

=round(a1)
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"aries0070" <[email protected]> wrote
in message news:[email protected]...
>
> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks
>
>
> --
> aries0070
> ------------------------------------------------------------------------
> aries0070's Profile:
http://www.excelforum.com/member.php...o&userid=25077
> View this thread: http://www.excelforum.com/showthread...hreadid=386182
>

That will give a syntax error, since you didn't include the second
argument.

Note that even =ROUND(A1,0) will give the wrong answer for the OP's
requirements. =ROUND(1.5,0) = 2, not 1.



In article <[email protected]>,
"Anne Troy" <[email protected]> wrote:

> =round(a1)
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "aries0070" <[email protected]> wrote
> in message news:[email protected]...
> >
> > I want to round of number for example:
> >
> > 1.5 = 1
> > 1.6 = 2
> >
> > What function should I use. Please help me. Thanks
> >
> >
> > --
> > aries0070
> > ------------------------------------------------------------------------
> > aries0070's Profile:
> http://www.excelforum.com/member.php...o&userid=25077
> > View this thread: http://www.excelforum.com/showthread...hreadid=386182
> >

Thanks. I blew that one, completely. Tried to get back to it when I saw they
wanted to round DOWN on .5, and got tied up. Thanks.
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> That will give a syntax error, since you didn't include the second
> argument.
>
> Note that even =ROUND(A1,0) will give the wrong answer for the OP's
> requirements. =ROUND(1.5,0) = 2, not 1.
>
>
>
> In article <[email protected]>,
> "Anne Troy" <[email protected]> wrote:
>
> > =round(a1)
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> > www.MyExpertsOnline.com
> >
> >
> > "aries0070" <[email protected]>
wrote
> > in message
news:[email protected]...
> > >
> > > I want to round of number for example:
> > >
> > > 1.5 = 1
> > > 1.6 = 2
> > >
> > > What function should I use. Please help me. Thanks
> > >
> > >
> > > --
> > > aries0070
> >
> ------------------------------------------------------------------------
> > > aries0070's Profile:
> > http://www.excelforum.com/member.php...o&userid=25077
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=386182
> > >

Thanks. I blew that one, completely. Tried to get back to it when I saw they
wanted to round DOWN on .5, and got tied up. Thanks.
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> That will give a syntax error, since you didn't include the second
> argument.
>
> Note that even =ROUND(A1,0) will give the wrong answer for the OP's
> requirements. =ROUND(1.5,0) = 2, not 1.
>
>
>
> In article <[email protected]>,
> "Anne Troy" <[email protected]> wrote:
>
> > =round(a1)
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> > www.MyExpertsOnline.com
> >
> >
> > "aries0070" <[email protected]>
wrote
> > in message
news:[email protected]...
> > >
> > > I want to round of number for example:
> > >
> > > 1.5 = 1
> > > 1.6 = 2
> > >
> > > What function should I use. Please help me. Thanks
> > >
> > >
> > > --
> > > aries0070
> >
> ------------------------------------------------------------------------
> > > aries0070's Profile:
> > http://www.excelforum.com/member.php...o&userid=25077
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=386182
> > >

That will give a syntax error, since you didn't include the second
argument.

Note that even =ROUND(A1,0) will give the wrong answer for the OP's
requirements. =ROUND(1.5,0) = 2, not 1.



In article <[email protected]>,
"Anne Troy" <[email protected]> wrote:

> =round(a1)
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "aries0070" <[email protected]> wrote
> in message news:[email protected]...
> >
> > I want to round of number for example:
> >
> > 1.5 = 1
> > 1.6 = 2
> >
> > What function should I use. Please help me. Thanks
> >
> >
> > --
> > aries0070
> > ------------------------------------------------------------------------
> > aries0070's Profile:
> http://www.excelforum.com/member.php...o&userid=25077
> > View this thread: http://www.excelforum.com/showthread...hreadid=386182
> >

One way:

=ROUND(A1,0)-(MOD(A1,1)=0.5)

In article <[email protected]>,
aries0070 <[email protected]>
wrote:

> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks

One way:

=ROUND(A1,0)-(MOD(A1,1)=0.5)

In article <[email protected]>,
aries0070 <[email protected]>
wrote:

> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks

Note: that works only for non-negative numbers. If you may be rounding
negative numbers using the same pattern, use

=ROUND(A1,0)-SIGN(A1)*(MOD(A1,1)=0.5)

In article <[email protected]>,
JE McGimpsey <[email protected]> wrote:

> One way:
>
> =ROUND(A1,0)-(MOD(A1,1)=0.5)

Note: that works only for non-negative numbers. If you may be rounding
negative numbers using the same pattern, use

=ROUND(A1,0)-SIGN(A1)*(MOD(A1,1)=0.5)

In article <[email protected]>,
JE McGimpsey <[email protected]> wrote:

> One way:
>
> =ROUND(A1,0)-(MOD(A1,1)=0.5)

Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.

=Round(a1,0) will behave as above. If you want to round
down at 1.5 you need to apply as

=Round(a1-1,0)


Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.

=Round(a1,0) will behave as above. If you want to round
down at 1.5 you need to apply as

=Round(a1-1,0)


Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

Not really:

=ROUND(A1-1,0) will round 1.6 to 1, not 2.

In article <[email protected]>,
elioch <elioch.1s0oq1@> wrote:

> If you want to round down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>

Not really:

=ROUND(A1-1,0) will round 1.6 to 1, not 2.

In article <[email protected]>,
elioch <elioch.1s0oq1@> wrote:

> If you want to round down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>

Not always! Some people, when rounding a number 5 as the test digit will
round to even value.
1.35 -> 1.4
1.45 -> 1.4
I have seen this called Banker's Rounding, New Math rounding, Australian
rounding.
Best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"elioch" <elioch.1s0oq1@> wrote in message
news:[email protected]...
>
> Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.
>
> =Round(a1,0) will behave as above. If you want to round
> down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>
>
> Elioch
>
>
> --
> eliochPosted from http://www.pcreview.co.uk/ newsgroup access
>

Not always! Some people, when rounding a number 5 as the test digit will
round to even value.
1.35 -> 1.4
1.45 -> 1.4
I have seen this called Banker's Rounding, New Math rounding, Australian
rounding.
Best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"elioch" <elioch.1s0oq1@> wrote in message
news:[email protected]...
>
> Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.
>
> =Round(a1,0) will behave as above. If you want to round
> down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>
>
> Elioch
>
>
> --
> eliochPosted from http://www.pcreview.co.uk/ newsgroup access
>

That type of rounding is very common in science, too, where it prevents
a bias away from zero.

There are many other flavors. See

http://support.microsoft.com/default...b;en-us;196652



In article <[email protected]>,
"Bernard Liengme" <[email protected]> wrote:

> I have seen this called Banker's Rounding, New Math rounding, Australian
> rounding.

A more bullet-proof approach is given in

http://groups-beta.google.com/group/...7fce6145b70d69

As an example of the difference, try rounding =1110*0.0865 to 2 decimal
places with microsoft's bround function and my ASTMround. Microsoft's
bround will round 96.0150000000000 to 96.01, where my ASTMround will
correctly round it to 96.02. Another example of the difference is
referenced in that post.

Jerry

JE McGimpsey wrote:

> That type of rounding is very common in science, too, where it prevents
> a bias away from zero.
>
> There are many other flavors. See
>
> http://support.microsoft.com/default...b;en-us;196652
>
>
>
> In article <[email protected]>,
> "Bernard Liengme" <[email protected]> wrote:
>
>
>>I have seen this called Banker's Rounding, New Math rounding, Australian
>>rounding.

A more bullet-proof approach is given in

http://groups-beta.google.com/group/...7fce6145b70d69

As an example of the difference, try rounding =1110*0.0865 to 2 decimal
places with microsoft's bround function and my ASTMround. Microsoft's
bround will round 96.0150000000000 to 96.01, where my ASTMround will
correctly round it to 96.02. Another example of the difference is
referenced in that post.

Jerry

JE McGimpsey wrote:

> That type of rounding is very common in science, too, where it prevents
> a bias away from zero.
>
> There are many other flavors. See
>
> http://support.microsoft.com/default...b;en-us;196652
>
>
>
> In article <[email protected]>,
> "Bernard Liengme" <[email protected]> wrote:
>
>
>>I have seen this called Banker's Rounding, New Math rounding, Australian
>>rounding.

That type of rounding is very common in science, too, where it prevents
a bias away from zero.

There are many other flavors. See

http://support.microsoft.com/default...b;en-us;196652



In article <[email protected]>,
"Bernard Liengme" <[email protected]> wrote:

> I have seen this called Banker's Rounding, New Math rounding, Australian
> rounding.

CORRECTION

=Round(a1,0) will behave as above. If you want to round down at 1.5 you
need to apply as

=Round(a1-0.1,0) it ishould be minus 0.1 and not minus 1 as stated
before.

Sorry

Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

CORRECTION

=Round(a1,0) will behave as above. If you want to round down at 1.5 you
need to apply as

=Round(a1-0.1,0) it ishould be minus 0.1 and not minus 1 as stated
before.

Sorry

Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

=round(a1)
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"aries0070" <[email protected]> wrote
in message news:[email protected]...
>
> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks
>
>
> --
> aries0070
> ------------------------------------------------------------------------
> aries0070's Profile:
http://www.excelforum.com/member.php...o&userid=25077
> View this thread: http://www.excelforum.com/showthread...hreadid=386182
>

=round(a1)
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"aries0070" <[email protected]> wrote
in message news:[email protected]...
>
> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks
>
>
> --
> aries0070
> ------------------------------------------------------------------------
> aries0070's Profile:
http://www.excelforum.com/member.php...o&userid=25077
> View this thread: http://www.excelforum.com/showthread...hreadid=386182
>

That will give a syntax error, since you didn't include the second
argument.

Note that even =ROUND(A1,0) will give the wrong answer for the OP's
requirements. =ROUND(1.5,0) = 2, not 1.



In article <[email protected]>,
"Anne Troy" <[email protected]> wrote:

> =round(a1)
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "aries0070" <[email protected]> wrote
> in message news:[email protected]...
> >
> > I want to round of number for example:
> >
> > 1.5 = 1
> > 1.6 = 2
> >
> > What function should I use. Please help me. Thanks
> >
> >
> > --
> > aries0070
> > ------------------------------------------------------------------------
> > aries0070's Profile:
> http://www.excelforum.com/member.php...o&userid=25077
> > View this thread: http://www.excelforum.com/showthread...hreadid=386182
> >

That will give a syntax error, since you didn't include the second
argument.

Note that even =ROUND(A1,0) will give the wrong answer for the OP's
requirements. =ROUND(1.5,0) = 2, not 1.



In article <[email protected]>,
"Anne Troy" <[email protected]> wrote:

> =round(a1)
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "aries0070" <[email protected]> wrote
> in message news:[email protected]...
> >
> > I want to round of number for example:
> >
> > 1.5 = 1
> > 1.6 = 2
> >
> > What function should I use. Please help me. Thanks
> >
> >
> > --
> > aries0070
> > ------------------------------------------------------------------------
> > aries0070's Profile:
> http://www.excelforum.com/member.php...o&userid=25077
> > View this thread: http://www.excelforum.com/showthread...hreadid=386182
> >

Thanks. I blew that one, completely. Tried to get back to it when I saw they
wanted to round DOWN on .5, and got tied up. Thanks.
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> That will give a syntax error, since you didn't include the second
> argument.
>
> Note that even =ROUND(A1,0) will give the wrong answer for the OP's
> requirements. =ROUND(1.5,0) = 2, not 1.
>
>
>
> In article <[email protected]>,
> "Anne Troy" <[email protected]> wrote:
>
> > =round(a1)
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> > www.MyExpertsOnline.com
> >
> >
> > "aries0070" <[email protected]>
wrote
> > in message
news:[email protected]...
> > >
> > > I want to round of number for example:
> > >
> > > 1.5 = 1
> > > 1.6 = 2
> > >
> > > What function should I use. Please help me. Thanks
> > >
> > >
> > > --
> > > aries0070
> >
> ------------------------------------------------------------------------
> > > aries0070's Profile:
> > http://www.excelforum.com/member.php...o&userid=25077
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=386182
> > >

Thanks. I blew that one, completely. Tried to get back to it when I saw they
wanted to round DOWN on .5, and got tied up. Thanks.
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> That will give a syntax error, since you didn't include the second
> argument.
>
> Note that even =ROUND(A1,0) will give the wrong answer for the OP's
> requirements. =ROUND(1.5,0) = 2, not 1.
>
>
>
> In article <[email protected]>,
> "Anne Troy" <[email protected]> wrote:
>
> > =round(a1)
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> > www.MyExpertsOnline.com
> >
> >
> > "aries0070" <[email protected]>
wrote
> > in message
news:[email protected]...
> > >
> > > I want to round of number for example:
> > >
> > > 1.5 = 1
> > > 1.6 = 2
> > >
> > > What function should I use. Please help me. Thanks
> > >
> > >
> > > --
> > > aries0070
> >
> ------------------------------------------------------------------------
> > > aries0070's Profile:
> > http://www.excelforum.com/member.php...o&userid=25077
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=386182
> > >

Thanks. I blew that one, completely. Tried to get back to it when I saw they
wanted to round DOWN on .5, and got tied up. Thanks.
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> That will give a syntax error, since you didn't include the second
> argument.
>
> Note that even =ROUND(A1,0) will give the wrong answer for the OP's
> requirements. =ROUND(1.5,0) = 2, not 1.
>
>
>
> In article <[email protected]>,
> "Anne Troy" <[email protected]> wrote:
>
> > =round(a1)
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> > www.MyExpertsOnline.com
> >
> >
> > "aries0070" <[email protected]>
wrote
> > in message
news:[email protected]...
> > >
> > > I want to round of number for example:
> > >
> > > 1.5 = 1
> > > 1.6 = 2
> > >
> > > What function should I use. Please help me. Thanks
> > >
> > >
> > > --
> > > aries0070
> >
> ------------------------------------------------------------------------
> > > aries0070's Profile:
> > http://www.excelforum.com/member.php...o&userid=25077
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=386182
> > >

That will give a syntax error, since you didn't include the second
argument.

Note that even =ROUND(A1,0) will give the wrong answer for the OP's
requirements. =ROUND(1.5,0) = 2, not 1.



In article <[email protected]>,
"Anne Troy" <[email protected]> wrote:

> =round(a1)
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "aries0070" <[email protected]> wrote
> in message news:[email protected]...
> >
> > I want to round of number for example:
> >
> > 1.5 = 1
> > 1.6 = 2
> >
> > What function should I use. Please help me. Thanks
> >
> >
> > --
> > aries0070
> > ------------------------------------------------------------------------
> > aries0070's Profile:
> http://www.excelforum.com/member.php...o&userid=25077
> > View this thread: http://www.excelforum.com/showthread...hreadid=386182
> >

One way:

=ROUND(A1,0)-(MOD(A1,1)=0.5)

In article <[email protected]>,
aries0070 <[email protected]>
wrote:

> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks

One way:

=ROUND(A1,0)-(MOD(A1,1)=0.5)

In article <[email protected]>,
aries0070 <[email protected]>
wrote:

> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks

Note: that works only for non-negative numbers. If you may be rounding
negative numbers using the same pattern, use

=ROUND(A1,0)-SIGN(A1)*(MOD(A1,1)=0.5)

In article <[email protected]>,
JE McGimpsey <[email protected]> wrote:

> One way:
>
> =ROUND(A1,0)-(MOD(A1,1)=0.5)

Note: that works only for non-negative numbers. If you may be rounding
negative numbers using the same pattern, use

=ROUND(A1,0)-SIGN(A1)*(MOD(A1,1)=0.5)

In article <[email protected]>,
JE McGimpsey <[email protected]> wrote:

> One way:
>
> =ROUND(A1,0)-(MOD(A1,1)=0.5)

Note: that works only for non-negative numbers. If you may be rounding
negative numbers using the same pattern, use

=ROUND(A1,0)-SIGN(A1)*(MOD(A1,1)=0.5)

In article <[email protected]>,
JE McGimpsey <[email protected]> wrote:

> One way:
>
> =ROUND(A1,0)-(MOD(A1,1)=0.5)

Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.

=Round(a1,0) will behave as above. If you want to round
down at 1.5 you need to apply as

=Round(a1-1,0)


Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.

=Round(a1,0) will behave as above. If you want to round
down at 1.5 you need to apply as

=Round(a1-1,0)


Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

Not really:

=ROUND(A1-1,0) will round 1.6 to 1, not 2.

In article <[email protected]>,
elioch <elioch.1s0oq1@> wrote:

> If you want to round down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>

Not really:

=ROUND(A1-1,0) will round 1.6 to 1, not 2.

In article <[email protected]>,
elioch <elioch.1s0oq1@> wrote:

> If you want to round down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>

Not always! Some people, when rounding a number 5 as the test digit will
round to even value.
1.35 -> 1.4
1.45 -> 1.4
I have seen this called Banker's Rounding, New Math rounding, Australian
rounding.
Best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"elioch" <elioch.1s0oq1@> wrote in message
news:[email protected]...
>
> Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.
>
> =Round(a1,0) will behave as above. If you want to round
> down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>
>
> Elioch
>
>
> --
> eliochPosted from http://www.pcreview.co.uk/ newsgroup access
>

Not always! Some people, when rounding a number 5 as the test digit will
round to even value.
1.35 -> 1.4
1.45 -> 1.4
I have seen this called Banker's Rounding, New Math rounding, Australian
rounding.
Best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"elioch" <elioch.1s0oq1@> wrote in message
news:[email protected]...
>
> Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.
>
> =Round(a1,0) will behave as above. If you want to round
> down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>
>
> Elioch
>
>
> --
> eliochPosted from http://www.pcreview.co.uk/ newsgroup access
>

That type of rounding is very common in science, too, where it prevents
a bias away from zero.

There are many other flavors. See

http://support.microsoft.com/default...b;en-us;196652



In article <[email protected]>,
"Bernard Liengme" <[email protected]> wrote:

> I have seen this called Banker's Rounding, New Math rounding, Australian
> rounding.

A more bullet-proof approach is given in

http://groups-beta.google.com/group/...7fce6145b70d69

As an example of the difference, try rounding =1110*0.0865 to 2 decimal
places with microsoft's bround function and my ASTMround. Microsoft's
bround will round 96.0150000000000 to 96.01, where my ASTMround will
correctly round it to 96.02. Another example of the difference is
referenced in that post.

Jerry

JE McGimpsey wrote:

> That type of rounding is very common in science, too, where it prevents
> a bias away from zero.
>
> There are many other flavors. See
>
> http://support.microsoft.com/default...b;en-us;196652
>
>
>
> In article <[email protected]>,
> "Bernard Liengme" <[email protected]> wrote:
>
>
>>I have seen this called Banker's Rounding, New Math rounding, Australian
>>rounding.

A more bullet-proof approach is given in

http://groups-beta.google.com/group/...7fce6145b70d69

As an example of the difference, try rounding =1110*0.0865 to 2 decimal
places with microsoft's bround function and my ASTMround. Microsoft's
bround will round 96.0150000000000 to 96.01, where my ASTMround will
correctly round it to 96.02. Another example of the difference is
referenced in that post.

Jerry

JE McGimpsey wrote:

> That type of rounding is very common in science, too, where it prevents
> a bias away from zero.
>
> There are many other flavors. See
>
> http://support.microsoft.com/default...b;en-us;196652
>
>
>
> In article <[email protected]>,
> "Bernard Liengme" <[email protected]> wrote:
>
>
>>I have seen this called Banker's Rounding, New Math rounding, Australian
>>rounding.

A more bullet-proof approach is given in

http://groups-beta.google.com/group/...7fce6145b70d69

As an example of the difference, try rounding =1110*0.0865 to 2 decimal
places with microsoft's bround function and my ASTMround. Microsoft's
bround will round 96.0150000000000 to 96.01, where my ASTMround will
correctly round it to 96.02. Another example of the difference is
referenced in that post.

Jerry

JE McGimpsey wrote:

> That type of rounding is very common in science, too, where it prevents
> a bias away from zero.
>
> There are many other flavors. See
>
> http://support.microsoft.com/default...b;en-us;196652
>
>
>
> In article <[email protected]>,
> "Bernard Liengme" <[email protected]> wrote:
>
>
>>I have seen this called Banker's Rounding, New Math rounding, Australian
>>rounding.

That type of rounding is very common in science, too, where it prevents
a bias away from zero.

There are many other flavors. See

http://support.microsoft.com/default...b;en-us;196652



In article <[email protected]>,
"Bernard Liengme" <[email protected]> wrote:

> I have seen this called Banker's Rounding, New Math rounding, Australian
> rounding.

That type of rounding is very common in science, too, where it prevents
a bias away from zero.

There are many other flavors. See

http://support.microsoft.com/default...b;en-us;196652



In article <[email protected]>,
"Bernard Liengme" <[email protected]> wrote:

> I have seen this called Banker's Rounding, New Math rounding, Australian
> rounding.

Not really:

=ROUND(A1-1,0) will round 1.6 to 1, not 2.

In article <[email protected]>,
elioch <elioch.1s0oq1@> wrote:

> If you want to round down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>

Not always! Some people, when rounding a number 5 as the test digit will
round to even value.
1.35 -> 1.4
1.45 -> 1.4
I have seen this called Banker's Rounding, New Math rounding, Australian
rounding.
Best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"elioch" <elioch.1s0oq1@> wrote in message
news:[email protected]...
>
> Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.
>
> =Round(a1,0) will behave as above. If you want to round
> down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>
>
> Elioch
>
>
> --
> eliochPosted from http://www.pcreview.co.uk/ newsgroup access
>

CORRECTION

=Round(a1,0) will behave as above. If you want to round down at 1.5 you
need to apply as

=Round(a1-0.1,0) it ishould be minus 0.1 and not minus 1 as stated
before.

Sorry

Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

CORRECTION

=Round(a1,0) will behave as above. If you want to round down at 1.5 you
need to apply as

=Round(a1-0.1,0) it ishould be minus 0.1 and not minus 1 as stated
before.

Sorry

Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

=round(a1)
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"aries0070" <[email protected]> wrote
in message news:[email protected]...
>
> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks
>
>
> --
> aries0070
> ------------------------------------------------------------------------
> aries0070's Profile:
http://www.excelforum.com/member.php...o&userid=25077
> View this thread: http://www.excelforum.com/showthread...hreadid=386182
>

One way:

=ROUND(A1,0)-(MOD(A1,1)=0.5)

In article <[email protected]>,
aries0070 <[email protected]>
wrote:

> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks

Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.

=Round(a1,0) will behave as above. If you want to round
down at 1.5 you need to apply as

=Round(a1-1,0)


Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

CORRECTION

=Round(a1,0) will behave as above. If you want to round down at 1.5 you
need to apply as

=Round(a1-0.1,0) it ishould be minus 0.1 and not minus 1 as stated
before.

Sorry

Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

=round(a1)
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"aries0070" <[email protected]> wrote
in message news:[email protected]...
>
> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks
>
>
> --
> aries0070
> ------------------------------------------------------------------------
> aries0070's Profile:
http://www.excelforum.com/member.php...o&userid=25077
> View this thread: http://www.excelforum.com/showthread...hreadid=386182
>

=round(a1)
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"aries0070" <[email protected]> wrote
in message news:[email protected]...
>
> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks
>
>
> --
> aries0070
> ------------------------------------------------------------------------
> aries0070's Profile:
http://www.excelforum.com/member.php...o&userid=25077
> View this thread: http://www.excelforum.com/showthread...hreadid=386182
>

=round(a1)
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"aries0070" <[email protected]> wrote
in message news:[email protected]...
>
> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks
>
>
> --
> aries0070
> ------------------------------------------------------------------------
> aries0070's Profile:
http://www.excelforum.com/member.php...o&userid=25077
> View this thread: http://www.excelforum.com/showthread...hreadid=386182
>

=round(a1)
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"aries0070" <[email protected]> wrote
in message news:[email protected]...
>
> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks
>
>
> --
> aries0070
> ------------------------------------------------------------------------
> aries0070's Profile:
http://www.excelforum.com/member.php...o&userid=25077
> View this thread: http://www.excelforum.com/showthread...hreadid=386182
>

That will give a syntax error, since you didn't include the second
argument.

Note that even =ROUND(A1,0) will give the wrong answer for the OP's
requirements. =ROUND(1.5,0) = 2, not 1.



In article <[email protected]>,
"Anne Troy" <[email protected]> wrote:

> =round(a1)
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "aries0070" <[email protected]> wrote
> in message news:[email protected]...
> >
> > I want to round of number for example:
> >
> > 1.5 = 1
> > 1.6 = 2
> >
> > What function should I use. Please help me. Thanks
> >
> >
> > --
> > aries0070
> > ------------------------------------------------------------------------
> > aries0070's Profile:
> http://www.excelforum.com/member.php...o&userid=25077
> > View this thread: http://www.excelforum.com/showthread...hreadid=386182
> >

That will give a syntax error, since you didn't include the second
argument.

Note that even =ROUND(A1,0) will give the wrong answer for the OP's
requirements. =ROUND(1.5,0) = 2, not 1.



In article <[email protected]>,
"Anne Troy" <[email protected]> wrote:

> =round(a1)
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "aries0070" <[email protected]> wrote
> in message news:[email protected]...
> >
> > I want to round of number for example:
> >
> > 1.5 = 1
> > 1.6 = 2
> >
> > What function should I use. Please help me. Thanks
> >
> >
> > --
> > aries0070
> > ------------------------------------------------------------------------
> > aries0070's Profile:
> http://www.excelforum.com/member.php...o&userid=25077
> > View this thread: http://www.excelforum.com/showthread...hreadid=386182
> >

That will give a syntax error, since you didn't include the second
argument.

Note that even =ROUND(A1,0) will give the wrong answer for the OP's
requirements. =ROUND(1.5,0) = 2, not 1.



In article <[email protected]>,
"Anne Troy" <[email protected]> wrote:

> =round(a1)
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "aries0070" <[email protected]> wrote
> in message news:[email protected]...
> >
> > I want to round of number for example:
> >
> > 1.5 = 1
> > 1.6 = 2
> >
> > What function should I use. Please help me. Thanks
> >
> >
> > --
> > aries0070
> > ------------------------------------------------------------------------
> > aries0070's Profile:
> http://www.excelforum.com/member.php...o&userid=25077
> > View this thread: http://www.excelforum.com/showthread...hreadid=386182
> >

That will give a syntax error, since you didn't include the second
argument.

Note that even =ROUND(A1,0) will give the wrong answer for the OP's
requirements. =ROUND(1.5,0) = 2, not 1.



In article <[email protected]>,
"Anne Troy" <[email protected]> wrote:

> =round(a1)
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "aries0070" <[email protected]> wrote
> in message news:[email protected]...
> >
> > I want to round of number for example:
> >
> > 1.5 = 1
> > 1.6 = 2
> >
> > What function should I use. Please help me. Thanks
> >
> >
> > --
> > aries0070
> > ------------------------------------------------------------------------
> > aries0070's Profile:
> http://www.excelforum.com/member.php...o&userid=25077
> > View this thread: http://www.excelforum.com/showthread...hreadid=386182
> >

Thanks. I blew that one, completely. Tried to get back to it when I saw they
wanted to round DOWN on .5, and got tied up. Thanks.
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> That will give a syntax error, since you didn't include the second
> argument.
>
> Note that even =ROUND(A1,0) will give the wrong answer for the OP's
> requirements. =ROUND(1.5,0) = 2, not 1.
>
>
>
> In article <[email protected]>,
> "Anne Troy" <[email protected]> wrote:
>
> > =round(a1)
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> > www.MyExpertsOnline.com
> >
> >
> > "aries0070" <[email protected]>
wrote
> > in message
news:[email protected]...
> > >
> > > I want to round of number for example:
> > >
> > > 1.5 = 1
> > > 1.6 = 2
> > >
> > > What function should I use. Please help me. Thanks
> > >
> > >
> > > --
> > > aries0070
> >
> ------------------------------------------------------------------------
> > > aries0070's Profile:
> > http://www.excelforum.com/member.php...o&userid=25077
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=386182
> > >

Thanks. I blew that one, completely. Tried to get back to it when I saw they
wanted to round DOWN on .5, and got tied up. Thanks.
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> That will give a syntax error, since you didn't include the second
> argument.
>
> Note that even =ROUND(A1,0) will give the wrong answer for the OP's
> requirements. =ROUND(1.5,0) = 2, not 1.
>
>
>
> In article <[email protected]>,
> "Anne Troy" <[email protected]> wrote:
>
> > =round(a1)
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> > www.MyExpertsOnline.com
> >
> >
> > "aries0070" <[email protected]>
wrote
> > in message
news:[email protected]...
> > >
> > > I want to round of number for example:
> > >
> > > 1.5 = 1
> > > 1.6 = 2
> > >
> > > What function should I use. Please help me. Thanks
> > >
> > >
> > > --
> > > aries0070
> >
> ------------------------------------------------------------------------
> > > aries0070's Profile:
> > http://www.excelforum.com/member.php...o&userid=25077
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=386182
> > >

Thanks. I blew that one, completely. Tried to get back to it when I saw they
wanted to round DOWN on .5, and got tied up. Thanks.
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> That will give a syntax error, since you didn't include the second
> argument.
>
> Note that even =ROUND(A1,0) will give the wrong answer for the OP's
> requirements. =ROUND(1.5,0) = 2, not 1.
>
>
>
> In article <[email protected]>,
> "Anne Troy" <[email protected]> wrote:
>
> > =round(a1)
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> > www.MyExpertsOnline.com
> >
> >
> > "aries0070" <[email protected]>
wrote
> > in message
news:[email protected]...
> > >
> > > I want to round of number for example:
> > >
> > > 1.5 = 1
> > > 1.6 = 2
> > >
> > > What function should I use. Please help me. Thanks
> > >
> > >
> > > --
> > > aries0070
> >
> ------------------------------------------------------------------------
> > > aries0070's Profile:
> > http://www.excelforum.com/member.php...o&userid=25077
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=386182
> > >

Thanks. I blew that one, completely. Tried to get back to it when I saw they
wanted to round DOWN on .5, and got tied up. Thanks.
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> That will give a syntax error, since you didn't include the second
> argument.
>
> Note that even =ROUND(A1,0) will give the wrong answer for the OP's
> requirements. =ROUND(1.5,0) = 2, not 1.
>
>
>
> In article <[email protected]>,
> "Anne Troy" <[email protected]> wrote:
>
> > =round(a1)
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> > www.MyExpertsOnline.com
> >
> >
> > "aries0070" <[email protected]>
wrote
> > in message
news:[email protected]...
> > >
> > > I want to round of number for example:
> > >
> > > 1.5 = 1
> > > 1.6 = 2
> > >
> > > What function should I use. Please help me. Thanks
> > >
> > >
> > > --
> > > aries0070
> >
> ------------------------------------------------------------------------
> > > aries0070's Profile:
> > http://www.excelforum.com/member.php...o&userid=25077
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=386182
> > >

One way:

=ROUND(A1,0)-(MOD(A1,1)=0.5)

In article <[email protected]>,
aries0070 <[email protected]>
wrote:

> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks

One way:

=ROUND(A1,0)-(MOD(A1,1)=0.5)

In article <[email protected]>,
aries0070 <[email protected]>
wrote:

> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks

One way:

=ROUND(A1,0)-(MOD(A1,1)=0.5)

In article <[email protected]>,
aries0070 <[email protected]>
wrote:

> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks

One way:

=ROUND(A1,0)-(MOD(A1,1)=0.5)

In article <[email protected]>,
aries0070 <[email protected]>
wrote:

> I want to round of number for example:
>
> 1.5 = 1
> 1.6 = 2
>
> What function should I use. Please help me. Thanks

Note: that works only for non-negative numbers. If you may be rounding
negative numbers using the same pattern, use

=ROUND(A1,0)-SIGN(A1)*(MOD(A1,1)=0.5)

In article <[email protected]>,
JE McGimpsey <[email protected]> wrote:

> One way:
>
> =ROUND(A1,0)-(MOD(A1,1)=0.5)

Note: that works only for non-negative numbers. If you may be rounding
negative numbers using the same pattern, use

=ROUND(A1,0)-SIGN(A1)*(MOD(A1,1)=0.5)

In article <[email protected]>,
JE McGimpsey <[email protected]> wrote:

> One way:
>
> =ROUND(A1,0)-(MOD(A1,1)=0.5)

Note: that works only for non-negative numbers. If you may be rounding
negative numbers using the same pattern, use

=ROUND(A1,0)-SIGN(A1)*(MOD(A1,1)=0.5)

In article <[email protected]>,
JE McGimpsey <[email protected]> wrote:

> One way:
>
> =ROUND(A1,0)-(MOD(A1,1)=0.5)

Note: that works only for non-negative numbers. If you may be rounding
negative numbers using the same pattern, use

=ROUND(A1,0)-SIGN(A1)*(MOD(A1,1)=0.5)

In article <[email protected]>,
JE McGimpsey <[email protected]> wrote:

> One way:
>
> =ROUND(A1,0)-(MOD(A1,1)=0.5)

Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.

=Round(a1,0) will behave as above. If you want to round
down at 1.5 you need to apply as

=Round(a1-1,0)


Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.

=Round(a1,0) will behave as above. If you want to round
down at 1.5 you need to apply as

=Round(a1-1,0)


Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.

=Round(a1,0) will behave as above. If you want to round
down at 1.5 you need to apply as

=Round(a1-1,0)


Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.

=Round(a1,0) will behave as above. If you want to round
down at 1.5 you need to apply as

=Round(a1-1,0)


Elioch


--
eliochPosted from http://www.pcreview.co.uk/ newsgroup access

Not really:

=ROUND(A1-1,0) will round 1.6 to 1, not 2.

In article <[email protected]>,
elioch <elioch.1s0oq1@> wrote:

> If you want to round down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>

Not really:

=ROUND(A1-1,0) will round 1.6 to 1, not 2.

In article <[email protected]>,
elioch <elioch.1s0oq1@> wrote:

> If you want to round down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>

Not really:

=ROUND(A1-1,0) will round 1.6 to 1, not 2.

In article <[email protected]>,
elioch <elioch.1s0oq1@> wrote:

> If you want to round down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>

Not really:

=ROUND(A1-1,0) will round 1.6 to 1, not 2.

In article <[email protected]>,
elioch <elioch.1s0oq1@> wrote:

> If you want to round down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>

Not always! Some people, when rounding a number 5 as the test digit will
round to even value.
1.35 -> 1.4
1.45 -> 1.4
I have seen this called Banker's Rounding, New Math rounding, Australian
rounding.
Best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"elioch" <elioch.1s0oq1@> wrote in message
news:[email protected]...
>
> Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.
>
> =Round(a1,0) will behave as above. If you want to round
> down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>
>
> Elioch
>
>
> --
> eliochPosted from http://www.pcreview.co.uk/ newsgroup access
>

Not always! Some people, when rounding a number 5 as the test digit will
round to even value.
1.35 -> 1.4
1.45 -> 1.4
I have seen this called Banker's Rounding, New Math rounding, Australian
rounding.
Best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"elioch" <elioch.1s0oq1@> wrote in message
news:[email protected]...
>
> Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.
>
> =Round(a1,0) will behave as above. If you want to round
> down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>
>
> Elioch
>
>
> --
> eliochPosted from http://www.pcreview.co.uk/ newsgroup access
>

Not always! Some people, when rounding a number 5 as the test digit will
round to even value.
1.35 -> 1.4
1.45 -> 1.4
I have seen this called Banker's Rounding, New Math rounding, Australian
rounding.
Best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"elioch" <elioch.1s0oq1@> wrote in message
news:[email protected]...
>
> Generally 1.4 is rounded rounded down and 1.5 and above is rounded up.
>
> =Round(a1,0) will behave as above. If you want to round
> down at 1.5 you need to apply as
>
> =Round(a1-1,0)
>
>
> Elioch
>
>
> --
> eliochPosted from http://www.pcreview.co.uk/ newsgroup access
>

That type of rounding is very common in science, too, where it prevents
a bias away from zero.

There are many other flavors. See

http://support.microsoft.com/default...b;en-us;196652



In article <[email protected]>,
"Bernard Liengme" <[email protected]> wrote:

> I have seen this called Banker's Rounding, New Math rounding, Australian
> rounding.

A more bullet-proof approach is given in

http://groups-beta.google.com/group/...7fce6145b70d69

As an example of the difference, try rounding =1110*0.0865 to 2 decimal
places with microsoft's bround function and my ASTMround. Microsoft's
bround will round 96.0150000000000 to 96.01, where my ASTMround will
correctly round it to 96.02. Another example of the difference is
referenced in that post.

Jerry

JE McGimpsey wrote:

> That type of rounding is very common in science, too, where it prevents
> a bias away from zero.
>
> There are many other flavors. See
>
> http://support.microsoft.com/default...b;en-us;196652
>
>
>
> In article <[email protected]>,
> "Bernard Liengme" <[email protected]> wrote:
>
>
>>I have seen this called Banker's Rounding, New Math rounding, Australian
>>rounding.

A more bullet-proof approach is given in

http://groups-beta.google.com/group/...7fce6145b70d69

As an example of the difference, try rounding =1110*0.0865 to 2 decimal
places with microsoft's bround function and my ASTMround. Microsoft's
bround will round 96.0150000000000 to 96.01, where my ASTMround will
correctly round it to 96.02. Another example of the difference is
referenced in that post.

Jerry

JE McGimpsey wrote:

> That type of rounding is very common in science, too, where it prevents
> a bias away from zero.
>
> There are many other flavors. See
>
> http://support.microsoft.com/default...b;en-us;196652
>
>
>
> In article <[email protected]>,
> "Bernard Liengme" <[email protected]> wrote:
>
>
>>I have seen this called Banker's Rounding, New Math rounding, Australian
>>rounding.