Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Pennies problem

Discussion in 'Information Technology' started by John, Jul 28, 2006.

  1. John

    John
    Expand Collapse
    Guest

    Hi

    I am calculating tax as follows;

    = [Net] * [TaxRate] / 100#

    Client complains that there is sometimes difference in pennies in tax
    calculation. Is there a better (more precise) way to calculate tax?

    Thanks

    Regards
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    How can I make decent and useful friends and from where ?? most people have a problem with me Sikh Youth Oct 23, 2013
    The Problem with Taking Too Many Vitamins Health & Nutrition Oct 18, 2013

  3. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    What sort of variable are you assigning this to or is it the Control Source
    of a textbox? Is the textbox limited to 2 decimals? If so, then Access will
    round to the 2nd decimal place. It may not round the way you want, you may
    be better off doing the rounding yourself in the equation. Also, if you add
    up several of these answers, what will be added is the actual answer to how
    ever many decimal places there are, not the displayed answer with 2 decimal
    places. Rounding the answer yourself before doing the addition would remove
    that problem.

    --
    Wayne Morgan
    MS Access MVP


    "John" <John@nospam.infovis.co.uk> wrote in message
    news:%23XMGkbmlGHA.4220@TK2MSFTNGP05.phx.gbl...
    > Hi
    >
    > I am calculating tax as follows;
    >
    > = [Net] * [TaxRate] / 100#
    >
    > Client complains that there is sometimes difference in pennies in tax
    > calculation. Is there a better (more precise) way to calculate tax?
    >
    > Thanks
    >
    > Regards
    >
     
  4. John

    John
    Expand Collapse
    Guest

    What would be a good way to round to 2 digits myself?

    Thanks

    Regards

    "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
    news:u1c2MtmlGHA.4540@TK2MSFTNGP04.phx.gbl...
    > What sort of variable are you assigning this to or is it the Control
    > Source of a textbox? Is the textbox limited to 2 decimals? If so, then
    > Access will round to the 2nd decimal place. It may not round the way you
    > want, you may be better off doing the rounding yourself in the equation.
    > Also, if you add up several of these answers, what will be added is the
    > actual answer to how ever many decimal places there are, not the displayed
    > answer with 2 decimal places. Rounding the answer yourself before doing
    > the addition would remove that problem.
    >
    > --
    > Wayne Morgan
    > MS Access MVP
    >
    >
    > "John" <John@nospam.infovis.co.uk> wrote in message
    > news:%23XMGkbmlGHA.4220@TK2MSFTNGP05.phx.gbl...
    >> Hi
    >>
    >> I am calculating tax as follows;
    >>
    >> = [Net] * [TaxRate] / 100#
    >>
    >> Client complains that there is sometimes difference in pennies in tax
    >> calculation. Is there a better (more precise) way to calculate tax?
    >>
    >> Thanks
    >>
    >> Regards
    >>

    >
    >
     
  5. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    John wrote:
    > Hi
    >
    > I am calculating tax as follows;
    >
    > = [Net] * [TaxRate] / 100#
    >
    > Client complains that there is sometimes difference in pennies in tax
    > calculation. Is there a better (more precise) way to calculate tax?
    >
    > Thanks
    >
    > Regards
    >
    >


    Too bad I didn't see sooner that this was multiposted to
    comp.databases.ms-access. I could have avoided giving this answer:

    http://groups.google.com/group/comp.databases.ms-access/msg/24b62fc73ccf04d8?hl=en&

    James A. Fortune
    MPAPoster@FortuneJames.com
     
  6. JPC

    JPC
    Expand Collapse
    Guest

    Try these:

    = (INT (( [Your Equation] ) * 100)) / 100

    = (INT (( [Your Equation] ) * 100) + 1) / 100

    Your tax calculation results in dollars and cents, and extra decimals.
    Multiply that by 100 moves the decimal point two places to the right.
    The INT function truncates any remaining decimal places.
    Dividing all that by 100 leaves you with dollars and only 2 decimals of cents.

    The "plus 1" above is sometimes used to assist rounding.

    "John" wrote:

    > What would be a good way to round to 2 digits myself?
    >
    > Thanks
    >
    > Regards
    >
    > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
    > news:u1c2MtmlGHA.4540@TK2MSFTNGP04.phx.gbl...
    > > What sort of variable are you assigning this to or is it the Control
    > > Source of a textbox? Is the textbox limited to 2 decimals? If so, then
    > > Access will round to the 2nd decimal place. It may not round the way you
    > > want, you may be better off doing the rounding yourself in the equation.
    > > Also, if you add up several of these answers, what will be added is the
    > > actual answer to how ever many decimal places there are, not the displayed
    > > answer with 2 decimal places. Rounding the answer yourself before doing
    > > the addition would remove that problem.
    > >
    > > --
    > > Wayne Morgan
    > > MS Access MVP
    > >
    > >
    > > "John" <John@nospam.infovis.co.uk> wrote in message
    > > news:%23XMGkbmlGHA.4220@TK2MSFTNGP05.phx.gbl...
    > >> Hi
    > >>
    > >> I am calculating tax as follows;
    > >>
    > >> = [Net] * [TaxRate] / 100#
    > >>
    > >> Client complains that there is sometimes difference in pennies in tax
    > >> calculation. Is there a better (more precise) way to calculate tax?
    > >>
    > >> Thanks
    > >>
    > >> Regards
    > >>

    > >
    > >

    >
    >
    >
     
  7. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    Try
    = Int(NumberToRound * 100) + 0.5) / 100

    Adding the 0.5 will round up any results where the 3rd decimal place is 5 or
    larger. This is the way sales tax is usually rounded and calculated.

    --
    Wayne Morgan
    MS Access MVP


    "John" <John@nospam.infovis.co.uk> wrote in message
    news:OKkknFnlGHA.3588@TK2MSFTNGP02.phx.gbl...
    > What would be a good way to round to 2 digits myself?
    >
    > Thanks
    >
    > Regards
    >
    > "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in
    > message news:u1c2MtmlGHA.4540@TK2MSFTNGP04.phx.gbl...
    >> What sort of variable are you assigning this to or is it the Control
    >> Source of a textbox? Is the textbox limited to 2 decimals? If so, then
    >> Access will round to the 2nd decimal place. It may not round the way you
    >> want, you may be better off doing the rounding yourself in the equation.
    >> Also, if you add up several of these answers, what will be added is the
    >> actual answer to how ever many decimal places there are, not the
    >> displayed answer with 2 decimal places. Rounding the answer yourself
    >> before doing the addition would remove that problem.
    >>
    >> --
    >> Wayne Morgan
    >> MS Access MVP
    >>
    >>
    >> "John" <John@nospam.infovis.co.uk> wrote in message
    >> news:%23XMGkbmlGHA.4220@TK2MSFTNGP05.phx.gbl...
    >>> Hi
    >>>
    >>> I am calculating tax as follows;
    >>>
    >>> = [Net] * [TaxRate] / 100#
    >>>
    >>> Client complains that there is sometimes difference in pennies in tax
    >>> calculation. Is there a better (more precise) way to calculate tax?
    >>>
    >>> Thanks
    >>>
    >>> Regards
    >>>

    >>
    >>

    >
    >
     
  8. John

    John
    Expand Collapse
    Guest

    Would Round([Net] * [TaxRate] / 100 + .0001 , 2) do the same?

    Regards

    "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
    news:eoA6sEylGHA.4196@TK2MSFTNGP04.phx.gbl...
    > Try
    > = Int(NumberToRound * 100) + 0.5) / 100
    >
    > Adding the 0.5 will round up any results where the 3rd decimal place is 5
    > or larger. This is the way sales tax is usually rounded and calculated.
    >
    > --
    > Wayne Morgan
    > MS Access MVP
    >
    >
    > "John" <John@nospam.infovis.co.uk> wrote in message
    > news:OKkknFnlGHA.3588@TK2MSFTNGP02.phx.gbl...
    >> What would be a good way to round to 2 digits myself?
    >>
    >> Thanks
    >>
    >> Regards
    >>
    >> "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in
    >> message news:u1c2MtmlGHA.4540@TK2MSFTNGP04.phx.gbl...
    >>> What sort of variable are you assigning this to or is it the Control
    >>> Source of a textbox? Is the textbox limited to 2 decimals? If so, then
    >>> Access will round to the 2nd decimal place. It may not round the way you
    >>> want, you may be better off doing the rounding yourself in the equation.
    >>> Also, if you add up several of these answers, what will be added is the
    >>> actual answer to how ever many decimal places there are, not the
    >>> displayed answer with 2 decimal places. Rounding the answer yourself
    >>> before doing the addition would remove that problem.
    >>>
    >>> --
    >>> Wayne Morgan
    >>> MS Access MVP
    >>>
    >>>
    >>> "John" <John@nospam.infovis.co.uk> wrote in message
    >>> news:%23XMGkbmlGHA.4220@TK2MSFTNGP05.phx.gbl...
    >>>> Hi
    >>>>
    >>>> I am calculating tax as follows;
    >>>>
    >>>> = [Net] * [TaxRate] / 100#
    >>>>
    >>>> Client complains that there is sometimes difference in pennies in tax
    >>>> calculation. Is there a better (more precise) way to calculate tax?
    >>>>
    >>>> Thanks
    >>>>
    >>>> Regards
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
  9. Edward Reid

    Edward Reid
    Expand Collapse
    Guest

    If you are doing any financial calculations where the pennies matter,
    then you MUST store your amounts in integers -- in other words, the
    number of pennies as an integer.

    If you don't understand why -- and based on your question, you probably
    don't -- then you shouldn't be coding calculations where the pennies
    matter.

    The complete answer is a good part of a book, at least a chapter or
    two, not a newsgroup posting.

    Edward
     
  10. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    The built-in Round() function does "banker's" or "scientific" rounding. It
    will take .5 and round it to the nearest even number. This reduces the
    rounding error in calculations. 1 number doesn't move (.0), 4 round down
    (.1 - .4), 4 round up (.6 - .9), and one goes up half the time and down half
    the time (.5). However, this isn't the way most folks round and isn't the
    way sales tax is usually calculated. Instead, these are usually calculated
    by rounding .5 up all of the time. Adding 0.0001 won't fix this problem,
    0.0149 should round down and adding 0.0001 to it will change it to 0.015
    which then will round up or down depending on where the even number is, in
    this case it would round to 0.02. Using the other equation, you get 01.49 +
    0.5 for 01.99. Taking the Int() you truncate the 1.99 to get 1 then divide
    by 100 to get 0.01. This has rounded down as it should have.

    --
    Wayne Morgan
    MS Access MVP


    "John" <John@nospam.infovis.co.uk> wrote in message
    news:uc3NhMzlGHA.1972@TK2MSFTNGP05.phx.gbl...
    > Would Round([Net] * [TaxRate] / 100 + .0001 , 2) do the same?
     
  11. John

    John
    Expand Collapse
    Guest

    You got two right brackets but only one left. Which one of the two right
    ones is redundant?

    Thanks

    Regards

    "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
    news:eoA6sEylGHA.4196@TK2MSFTNGP04.phx.gbl...
    > Try
    > = Int(NumberToRound * 100) + 0.5) / 100
    >
    > Adding the 0.5 will round up any results where the 3rd decimal place is 5
    > or larger. This is the way sales tax is usually rounded and calculated.
    >
    > --
    > Wayne Morgan
    > MS Access MVP
    >
    >
    > "John" <John@nospam.infovis.co.uk> wrote in message
    > news:OKkknFnlGHA.3588@TK2MSFTNGP02.phx.gbl...
    >> What would be a good way to round to 2 digits myself?
    >>
    >> Thanks
    >>
    >> Regards
    >>
    >> "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in
    >> message news:u1c2MtmlGHA.4540@TK2MSFTNGP04.phx.gbl...
    >>> What sort of variable are you assigning this to or is it the Control
    >>> Source of a textbox? Is the textbox limited to 2 decimals? If so, then
    >>> Access will round to the 2nd decimal place. It may not round the way you
    >>> want, you may be better off doing the rounding yourself in the equation.
    >>> Also, if you add up several of these answers, what will be added is the
    >>> actual answer to how ever many decimal places there are, not the
    >>> displayed answer with 2 decimal places. Rounding the answer yourself
    >>> before doing the addition would remove that problem.
    >>>
    >>> --
    >>> Wayne Morgan
    >>> MS Access MVP
    >>>
    >>>
    >>> "John" <John@nospam.infovis.co.uk> wrote in message
    >>> news:%23XMGkbmlGHA.4220@TK2MSFTNGP05.phx.gbl...
    >>>> Hi
    >>>>
    >>>> I am calculating tax as follows;
    >>>>
    >>>> = [Net] * [TaxRate] / 100#
    >>>>
    >>>> Client complains that there is sometimes difference in pennies in tax
    >>>> calculation. Is there a better (more precise) way to calculate tax?
    >>>>
    >>>> Thanks
    >>>>
    >>>> Regards
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
  12. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    Oops, sorry.

    = Int((NumberToRound * 100) + 0.5) / 100

    Technically, the inner set is redundant since multiplication will be done
    before addition.

    --
    Wayne Morgan
    MS Access MVP


    "John" <John@nospam.infovis.co.uk> wrote in message
    news:%23$hv0F8lGHA.4768@TK2MSFTNGP03.phx.gbl...
    > You got two right brackets but only one left. Which one of the two right
    > ones is redundant?
     

Share This Page