AUTOSUM Coming up with an incorrect total

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: AUTOSUM Coming up with an incorrect total

  1. #1
    New Member
    Join Date
    Sep 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    81,455
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    81,455
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    42,657
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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]

  10. #10
    New Member
    Join Date
    Aug 2015
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AUTOSUM Coming up with an incorrect total

      
    Another reason the autosum and the sum shown in the bottom right status bar in Excel do not match could be because you have hidden rows!

    The "sum of selected cells" only uses visible cells

User Tag List

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