Software

Office challenge: Why is SUM() returning different results for the same values?

Learn the answer to last week's Excel challenge and try your skills at another Excel challenge--the mysterious SUM() function evaluates the same values but returns different results!

Two SUM() functions evaluate similar values but return different results--how can this be? For instance, the SUM() functions in cells B4 and C4 both evaluate the values 1, 1, and 1. The function in cell B4 returns the correct result, 3, but the function in cell C4 returns 2. What's the problem (and there might be more than one cause)?

Last week we asked…

Why is Excel creating two decimal places for input values? BJWiltzius was the first to respond with the correct answer: Someone has enabled Excel's Fixed Decimal Places option. When this option enabled, Excel adds decimal places to every input value, regardless of your intentions. For example, if the option is set to 2, Excel changes an input value of 123 to 1.23. Enabling the feature is a good way to facilitate quick data entry when consistent decimal values are required. Most likely you'll enable it as needed and then turn it off when you don't. Forgetting to disable the feature often causes confusion the next time someone tries to enter values. Checking the cells' format is the key to quickly identifying the problem. Very few workbooks will be improved by enabling the feature permanently.

In Excel 2003, you'll find this option, Fixed Decimal Places, as follows:

  1. Choose Options from the Tools menu.
  2. Click the Edit tab.

In Excel 2007 and 2010, do the following:

  1. In Excel 2007, click the Office button and then click the Excel Options button. In Excel 2010, click the File tab and then click Options under Help.
  2. Select the Advanced option in the left pane.
  3. In the Editing section, check or uncheck the Automatically Insert A Decimal Point option.

This is an application option and works with all new workbooks once changed. Congratulations to BJWiltzius for the quick and correct response and thanks to everyone for playing along!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

82 comments
alcolley
alcolley

One other formatting possibility is that one cell was formatted as text and the entire column was aligned right. I imagine this occurs more frequently than the error acknowledged by the author.

sam
sam

It's something to do with formatting of the data cells, as many people have suggested already. For whatever reason (and it doesn't have to make any sense, Excel just has foibles like this) the cell doesn't think it's a number value. Sometimes the old *1 trick works, other times you just have to jump into the formula bar, press Enter and "Hey Presto" Excel has grown it's brain back. Funnily enough I'm experiencing these exact symptoms in a spreadsheet I'm working on at the moment, so if anyone wants to see a real life example I can send it over!

ppg
ppg

Just to throw another unlikely option - Cell C3 could contain a zero and there is a Label or TextBox containing "1" covering the 0.

bbbtechrep
bbbtechrep

I haven't read the other comments, but one reason could be values of 2 cells in the C column are ".5" but are formatted with 0 decimal places. They display as "1" but SUM returns the actual value.

happymedia_dz
happymedia_dz

One possibility is that one of the number, in the second column, is negative (-1) but the format don't show the sign.

jeremyo22
jeremyo22

It's one of 3 possible answers: 1. Formatted decimal numbers to appear as 1 but sum to 2. 2. Auto-Calc is not active, and the sheet was changed without a recalc. 3. One of the 1's are a lable, not a number. For this to be correct, the error would have to be dismissed, and the cell formatted to right alignment. Anyone have other thoughts?

Bob G Beechey
Bob G Beechey

At least two possible reasons 1) Cells C1 to C3 contain numbers between 0.5 and 0.8 but column is formatted as 0 decimal places 2) Cells C1 to C3 all conyain 1 but C3 is formarres as right-aligned text

gluhas
gluhas

The cells in column B each hold a one. Two of the cells in column C actually have a .5 but the column is formatted to show no decimal places. .5 + .5 + 1 = 3

ppg
ppg

Allow me to present one of my pet peeves. Please read the comments other people have posted before you respond. In this case only 6 different solutions were presented and repeated multiple times. To be specific the solutions are rounding error - 20 times text - 10 times typo in sum - 4 times manual calculation - 2 times other format - 1 time circular reference - 1 time

kenkenmdr
kenkenmdr

One of the 1s is not numeric. Solution: Type a 1 somewhere on the sheet. Copy this cell. Select the three 1s and PasteSpecial/Multiply. Now the sums are the same. Ken

JPcutter
JPcutter

This may have already been posted but... If the true values are something like 0.7 and the format is zero (0) decimal places, the values are displayed as 1, like in this problem. When 0.7 is added three times, the value is 2.1 and the value displayed is also rounded to 2. To verify cell values, click on a cell and look at the value in the formula bar.

Rowlen
Rowlen

Should one of the values are entered as text ('1), Excel will not calculate this cell. This is a favorite sabotage trick

doug
doug

1. Numeric Formatting. When numbers are displayed without decimals, their true values may not be represented correctly B1=1; B2=1; B3=1; B4=Sum(B1:B3)=3 C1=0.5; C2=0.5; C3=0.5; C4=Sum(C1:C3)=1.5 Formatted without displaying decimal values, it appears Rounded and appears C1=1; C2=1; C3=1; C4=Sum(C1:C3)=3 2. Inconsistency in Formulas B4 = Sum(B1:B3) C4 = Sum(C1:C2) or Sum(C2:C3) or other variations 3. One of the numeric values is not numeric but text. For example C1='1 (text) C2=1 (numeric) C3=1 (numeric) C4=Sum(C1:C3)=2 4. Calcuation Options is set to manual and C4 has not been recalculated.

e-newsletters
e-newsletters

First, data cells are formatted to show 0 decimal places. Actual data in column C is comprised of two or three numbers less than 1 but greater than 0.5. Data in Column B are actual whole numbers and/or sum to an amount >= 2.5.

fanfei.clark
fanfei.clark

It is again because of Decrease Decimal. The first column may be all 1's, the second column may be all 0.6's. So 0.6+.6+.6 = 1.8 rounded to 2 :)

bob.arrendell
bob.arrendell

Assuming that the numbers shown in each cell are exactly what is entered and no rounding off takes place, one possible answer is that the full range is not included in the summation

markbeckstrom
markbeckstrom

I learned the hard way years ago that what is displayed in the cell in not always what it really is. This is really a simple issue. If the cells are set to show no decimal Excel will round by default rules. so the Sum will be different if the "hidden" true value is something else than a whole number. Remember, Excel will add the real values not the displayed values.

ppg
ppg

If you have turned off automatic calculation you can get the display shown. Enter All the values and formula except put a 0 in cell C3. If you do a calculation the sums will be 3 and 2. Then enter 1 in C3 and don't do a calculation. The sums will still show as 3 and 2.

kargall
kargall

Ifg the values in the secomd column are all .5 but the format of the column is fixed at zero decimal places, then the actual sum, 1.5, would be displayed as 2

tuckerws
tuckerws

It may be possible that the values being added are not the same and the discrepancy is because the cells are being rounded with the decimals cut off. The value that displays and the value that is calculated are not always the same. Ex: if Column C contains actual values C1=.5 C2=.5 and C3=.5 and the cells and sum are rounded with formatting, then this result of 1.5 being rounded up could occur. The best way to check is to go to each cell and then check the corresponding value in the formula bar which would not be rounded. You could then go back in and check your formatting. Billy T

steve.lott
steve.lott

If you use a Colon in the first column it will add up everything in the range (b1:b3). This included b1,b2 and b3 ging the answer of 3 By using a comma in the second column it will add only the cells explicitly defined (c1,c3) only c1 and c3 give the answer of 2

david.taylor
david.taylor

One of the values in the second column could contain a 'Circular Reference'. This will always create 'unusual' answer in =SUM whether you use AUTOSUM or not.

HannesNel
HannesNel

Rounding up from 0.5 for all three cells, and sum() cell rounding up 1.5 to 2.

ebay.duckworth
ebay.duckworth

2007 flags text "1" as a possible error. (If you are automatically checking for errors.) You can select "ignore" the error and the flag will disappear.

PatriciaT
PatriciaT

Sometimes, my applications just get in really bad moods and mess with things intentionally, especially Excel 2007. We're working on developing the virtues of helpfulness, patience, and restraint.

stapleb
stapleb

Hi Susan. Maybe all the 1's are not actually 1 but some are less than 1, eg .9 and some more 1.4 etc. Then all numbers have been formatted so as to not show decimal places, and thus are rounded up or down. Sum ignores the displayed values and sums the actual values. Don't know if anyone else came upwith this idea as I haven't read all the comments yet.

mileyrf
mileyrf

Possi: ble Solutions 1. One of the numbers is not formated as a number. (nonnumeric values in references are not translated, the values in the column above are ignored.) 2. The sum function is not summing all the cells (c1:c3. Examples: [=sum(c1:c2), =sum(c2:c3), =sum(c1,c3)] 3. If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text in the array or reference are ignored.

peter.burtenshaw
peter.burtenshaw

Numbers are displayed as "1" but they could be anything from 0.500000000000000001 to 1.499999999999999 which would give different results. Ue the Precision as Displayed option to remove the problem.

dutch_gemini
dutch_gemini

According to the title and part of the description, the SUM() in C4 and D4 works on the *SAME* values, this we can assume to be "=SUM(1,1,1)". However, the introduction reads "Two SUM() functions evaluate similar values". So what's the point Susan? Are the values the same or are they similar? In the first case, the SUM() will definitively return the *same* result. In the second case, the SUM() will computes the correct sum using [absolute] values that are being displayed as 1 as a result a 0-decimal places, numeric format. Dutch

rkendsley
rkendsley

In Excel 2003 SP3, if I have a cell and it is formated as Numeric with 2 decimal places and enter 123 I get 123.00, not 1.23. Maybe I missing something here or misunderstanding the authors explanation?

jotade
jotade

If you write a 1 as '1 the result is 2 Jotade

rkryz
rkryz

The spreadsheet has been formated for zero decimal places. If you places a "0.6" values in the right hand side, the display is a "1" and the sum will show as "2".

eatonp
eatonp

Column "C" has the values 0.5, 0.5 and 0.5. When you sum them, you get 1.5. Then the format of the cells was changed to "Number" with "0" decimal places and so each of the cells round up (even though behind the scenes they have their original values).

zorglup37
zorglup37

With Excel 2003, the 3 proposed solutions (one cell containing right-justified text, numbers with 0 decimal place, sum(c1;c3) ) give the wrong answer...

ltreff
ltreff

Simple solution. Lightly highlight a cell when the underlying value differs from the displayed value.

julian.brunt
julian.brunt

It is because you have values like 0.66666666666666666666666666666667 displayed with 0 decimal places in the column C This will display as 1 (rounding up) but sum correctly to 2

davidchall
davidchall

One of the cells contains text...

wehmeier
wehmeier

My first thought was that one of the cells in column C was formatted as a text string, or at least something other than a value.

alcolley
alcolley

One other formatting possibility is that one cell was formatted as text and the entire column was aligned right. I imagine this occurs more frequently than the error acknowledged by the author.

sebair
sebair

I agree 100%. I just plowed through almost 40 posts only to see the same answer over and over again. No joy. Although, I suppose some could have posted at the same time (or close) so that the extras weren't there when they submitted. Still, 20 rounding error answers!

blogs
blogs

Spreadsheet is the pre PC term for the large sheets of paper on which bookkeepers 'spread' their figures. Adding machines were used to total the columns. The paper tape from the adding machine would be attached to the spreadsheet to validate the total. Those of dubious honesty learned to produce any number they wanted on the adding machine tape by rolling the paper forward, entering additional figures, rolling the tape back, then hitting total and transferring their altered result to the spreadsheet. The same technique works with Excel, just include a few additional (hidden or hard to find) cells in a complex SUM() formula and one can produce any desired total. Or, one could omit a few cells, although this is usually carelessness rather than dishonesty. I've found too many incorrect SUM() at the bottom of Excel columns to ever assume that the total was actually the sum of the column above. I always at least spot check!

compulew
compulew

If the columns are formatted properly, ie; number format without decimals, you should yield the correct results. My suggestion is that you can also drag, seeing the + symbol when hovering over the bottom right most portion of the cell, the formula from one cell location to the other which will maintain referential integrity for the calculated columns.

stapleb
stapleb

PatriciaT of course your apps get in a really bad mood. They sense your rising tension and develop an error proportional to your level of angst. Another thing you need to do is check see if it's a full moon. But, you survive it all, learn something new about working with your product, and go home with a really good reason to have a glass of something strong.

ssharkins
ssharkins

I purposely used the term "values" instead of numbers. In that sense, same, is correct as used. :) In this particular case, I would consider both the numeric and text 1 as the same "value" -- they have different characteristics, but they're the same value. We could argue that point and I agree, it's a bit of muddy water. ;) 1 and 1 are the "same" even if Excel treats one as text and one as a number. Hope that clarifies things. Getting some really interesting responses though -- remember, I said there could be more than one reason, so so far, everyone's been right on the money.

ultimitloozer
ultimitloozer

Formatting a cell as numeric with a fixed number of decimal places is not the same as autmatically inserting a decimal point which is what the author is talking about.

spadurar
spadurar

For masson and wehmeier: have you checked this in excel? Because in v.2003 formatting a cell to text has no effect on the SUM(). If you introduce text (blanks) before or after the 1, the alignment will be changed, and I cannot observe that in the image.

maj37
maj37

I am pretty sure it has always been that Excel will not sum text even if it looks like a value and I just tested it in Excel 2007. It looks just like the sample including the 3 and the 2. maj

dhays
dhays

I use an Excel spreadsheet whee the dates are input as text values, however I can add or subtract them to get the information I need, you just have to tell Excell to treat anthing that looks like a date as a date. I haven't tried any of the suggestions, but it seems likely that just putting in a text value waon't chenge things, especially if the program is told to treat it as a number.

cboylan43
cboylan43

You have to set the cell to Text first, before entering the 1. If you have entered the 1 already, you are right, changing the format will not change the result.

cawwilsontx
cawwilsontx

(97 is the only version I have) Making one of the numbers text has no effect, plus the correct sum of 3 is displayed as text also.

Editor's Picks