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.
Another option, which I've found to be more versatile: the value() function. It can usually be added without much effort, and without side effects.
example: change from '7' to '=value(7)'
Mate, I never knew this feature existed and certainly never changed it from the default to manual. One day a couple of weeks ago, my spreadsheets started to behave differently. First time I have seen this in 30 years of working with spreadsheets. I'm assuming Microsoft made an update to Office 365 and changed the default to manual. No other way it could have happened.
As much as I know, the Calculation feature ("Automatic/Manually") added from Excel 2007. The default calculation mode is Automatic. You can set the default Calculation mode Manual from File | Options | Formulas.
This feature is very useful because it's very disturbing to work with large number of data (Around 500000) with formulas. If you change any cell then it takes time to update all 500000 rows. And it takes long time which is unexpected in my working hour.
All of a sudden, I find that spreadsheets I have been using for over a decade no longer calculate simple math correctly. Simple math! I have a column of numbers that I add up using =sum(..) function, which has been the same for decades. i started noticing bogus output a few weeks ago in one spreadsheet and have been scratching my head over it. Now, when I change the numbers in the column being added, the output cell remains the same! WTF???? I thought I had a buggy spreadsheet or maybe a virus. Each time I wanted to do the calculation, I had to clear the output cell and reenter the function. Today, I saw the same behavior in another spreadsheet that I have used for years and years. So I called Microsoft. Those idiots batted me around between support "professionals" for a long while and I did some searching while I was on hold until I found your post, checked the settings, and surprise surprise surpise - you are correct. It was set to "manual" instead of "automatic"! But the question is, when was this "feature" added, and the bigger question is who the f would ever think of such a stupid idea and set it as the default without warning the entire world that their spreadsheets would no longer perform basic math???? my God. I am at a loss. A loss! How many errors have I been putting out to my business partners with my spreadsheet output and for how f-ing long? I simply cannot imagine any purpose for setting it to "manual". That is why we use spreadsheets! To quickly perform mathematical functions. If I wanted to do the work manually, I'd use a goddam adding machine for christsakes. What idiot though of this and when did Microsoft(brains) foist this upon us? I certainly never changed this setting in Excel and never heard of it before, nor would I ever think there was a want or a need out there to manually calculate data in a spreadsheet! Has the entire world been taken over by idiots? Has Microsoft been infected by morons? Or have I been unknowingly transported into a parallel dimension of idiots when before I lived in one where a) no one with even a high school education would even think of such a "feature" and b) Donald Trump was just a bankrupt failed businessman, not president of the US. Please, please tell me how I can get back to the other dimension where at least these two elements of my new reality do not exist. Please!
Ronald wrote:
I have Microsoft office 365 - Excel is not adding certain cells correctly[.] e.g. - say - =sum(i43:i59) = should be £32000 - Excel is adding it as £43000[.] Found that it is including cells i63-i72
If you wrote I43:I59 as shown, it is not including I63:I72.
(Unless you mean that some values in I43:I59 are the same as some values in I63:I72, either by coincidence or by design.)
Two common reasons why the SUM might be wrong.
=ISTEXT(I43) into J43 and copy down through J59. It is not sufficient to look at the cell format; we can enter text into a cell with a numeric format. The remedy depends on the content of I43:I59.
If you cannot correct the problem yourself, I suggest that you upload an example Excel file (redacted) that duplicates the problem to a file-sharing website (e.g. box.net/files), and post the public/share URL in a response here. (Actually, you should start your own question, not piggyback someone else's.) Test the URL first, being careful to log out of the file-sharing website.