This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I have an Excel formula issue in the formula not resulting in the correct sum, but it is not a rounding error; rather it is off by an entire cell amount. As an example, I was adding eight cells with the value of $3,001.53 which should have resulted in a total of $24,012.24, but instead I got $21,010.71 (off by one cell value). I triple-checked to make sure that the formula included all cell addresses I was trying to add, which it did. I tried deleting the formula and doing it over again, saving the spread sheet as different versions of Excel, putting the formula in a different cell location, all to no avail. There were other similar errors in the spread sheet. FYI, I am not adding concecutive cells, rather, selecting certain cells using Ctrl, left click.
I'm using Excel 2010 Version 14.1.6129.5000 (32-bit).
Please advise.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
I had this problem too. Go to Options menu under the File Tab.
Under Excel options there should be one that says Formulas and under that/beside that Calculation Options and Workbook Calculation. Make sure Automatic is checked.
Wow! What a life saver. Thanks.
I had this issue and tried many fixes. After wasting tons of time I decided to copy the whole table to a googledoc. I updated the formulas and it worked! I then saved the file as an excel file.
Had this issue and was ready to punch a hole through my monitor. Auto Sum gave me an incorrect total, but if I added manually or manually put the cells into an addition formula they gave me the correct total. I luckily knew which range of cells were suspect, so I made sure to change their formatting from General to Numbers. However, this didn't change anything. I had to re-enter the numbers in the cells and only then did they work in the Auto Sum formula.
So I guess that even if you change a cells format it can decide not to change the format of what was in there previously?
You do not offer sufficient detail for a knowledgable explanation of the problem. In fact, even the problem ("incorrect total") is too vague.
If you would like an explanation, I suggest that upload an example Excel file (redacted) that demonstrates the problem to a file-sharing website (e.g. box.net/files) and post the public/share URL in a response. Test the download URL first, being careful to log out of the file-sharing website.
The devil might be in subtle details that might be overlooked or misrepresented.
Justin wrote:
Auto Sum gave me an incorrect total, but if I added manually or manually put the cells into an addition formula they gave me the correct total. I had to re-enter the numbers in the cells and only then did they work in the Auto Sum formula.
That could describe a subtle anomaly that arises with binary computer arithmetic. Or it could describe a lack of understanding of your formulas and the need for explicit rounding.
A common mistake is to think that the displayed value is the actual cell value. For example, if the formula is =123.45/2 formatted to display 2 decimal places, the result might appear to be 61.73, but it is actually 61.725. When you manually use or re-enter 61.73, you "correct" the problem as you see it.
Alternatively, a common anomaly arises because of the type of internal binary representation that Excel uses. As a consequence, for example, =IF(34.56-34=0.56,TRUE) returns FALSE(!).
Justin wrote:
I made sure to change their formatting from General to Numbers. However, this didn't change anything. [....] So I guess that even if you change a cells format it can decide not to change the format of what was in there previously?
Usually [1], the cell format only changes the appearance of a numeric value; it does not change the actual value.
[1] An exception is when the "Precision as displayed" option is enabled, which is not recommended. If PAD is set, changing the precision of the cell value can change the cell value and the value of dependent formulas by changing the way that the cell value is rounded.
Well, not always. I had to remove $ from all the numbers and I got the correct number. I don't know why that affected it, but then I had to download the report, so it may have been something that happened in the download.