Results 1 to 9 of 9

AUTOSUM Coming up with an incorrect total

This is a discussion on AUTOSUM Coming up with an incorrect total within the Excel Questions forums, part of the Question Forums category; This is certainly a new one for me -- I have a simple worksheet - first two columns are monthly ...

  1. #1
    New Member
    Join Date
    Sep 2002
    Posts
    11

    Default AUTOSUM Coming up with an incorrect total

    This is certainly a new one for me -- I have a simple worksheet - first two columns are monthly charges - the third column I have a formula to add the first two columns and multiply by the tax rate for the county in question. The fourth column adds the three amounts across. At the bottom of the spreadsheet I am adding the amounts in each column, but three of the columns have incorrect amounts! I have check the format - they are all currency. I have tried manually typing =sum etc - no matter how I try it the amounts in those three columns are wrong. What else can I check? Thanks!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,196

    Default Re: AUTOSUM Coming up with an incorrect total

    Care to post the SUM formula?

  3. #3
    New Member
    Join Date
    Sep 2002
    Posts
    11

    Default Re: AUTOSUM Coming up with an incorrect total

    =(B126+C126)*0.0725 (for the tax amount)
    =SUM(E2:E126) (for the bottom of the column)

    nothing fancy as you can see.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,196

    Default Re: AUTOSUM Coming up with an incorrect total

    Quote Originally Posted by ESCIV
    =(B126+C126)*0.0725 (for the tax amount)
    =SUM(E2:E126) (for the bottom of the column)

    nothing fancy as you can see.
    Compare the result of

    =SUMPRODUCT(E2:E126+0)

    with the result of

    =SUM(E2:E126)

    Any difference?

  5. #5
    New Member
    Join Date
    Sep 2002
    Posts
    11

    Default Re: AUTOSUM Coming up with an incorrect total

    Thanks but no - I just had to two these 2 monthly reports again and the same thing has happened - I'm off by pennies, but the total must be exact.

  6. #6
    IML
    IML is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,744

    Default Re: AUTOSUM Coming up with an incorrect total

    Does it look better if you click "precision as displayed" under tools, option, calculation.

  7. #7
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,256

    Default Re: AUTOSUM Coming up with an incorrect total

    There are probably some rounding issues going on. Check out IML's response. Personally, I usually use the round function to make sure that everything is rounded off to the nearest penny to avoid problems:

    =ROUND((B126+C126)*0.0725,2)

    Keep in mind that what is displayed on the speadsheet is not always the actual content in the cell!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    New Member
    Join Date
    Sep 2002
    Posts
    11

    Default Re: AUTOSUM Coming up with an incorrect total

    You are awesome!! Thank you so much! I never knew what that "precision as displayed" was for! I can keep the rest of my hair on my head now! God bless!

  9. #9
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: AUTOSUM Coming up with an incorrect total

    Please be aware that the Precision as Displayed utility ewill, upon saving, permanently alter your underlying data. It will do so for the entire workbook, not just the sheet/cell in question.
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com