AUTOSUM Coming up with an incorrect total

ESCIV

New Member
Joined
Sep 25, 2002
Messages
11
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!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
=(B126+C126)*0.0725 (for the tax amount)
=SUM(E2:E126) (for the bottom of the column)

nothing fancy as you can see.
 
Upvote 0
ESCIV said:
=(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?
 
Upvote 0
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. :cry:
 
Upvote 0
Does it look better if you click "precision as displayed" under tools, option, calculation.
 
Upvote 0
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!
 
Upvote 0
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! :biggrin:
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top