Results 1 to 6 of 6
Thread: use of =sum

20090804, 11:07 #1
 Join Date
 May 2002
 Posts
 413
 Thanks
 1
 Thanked 0 Times in 0 Posts
The attached worksheet used the =sum function but only had one cell in the range; e.g., =SUM(B11,D11,F11,H11,L11). The calculate "sign' is always on. If you edit one of the Column L sum cells, all of the other column L sum cells change. The column M sum cells appear to work correctly. However, if you edit one of the M column sum cells the L column sum cells change.
If I copy the worksheet and reorder all of the columns to be together, the =SUM(B11:F11) works fine (2nd worksheet). Also, using =+B11+D11+F11+H11+J11 works correctly; i.e., columns N and O.
I'm flummoxed as to why this is happening.
thanks for the help.

20090804, 11:17 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
[quote name='jepalmer' post='787526' date='04Aug2009 09:07'][/quote]
You're aware that in the Test sheet, the last argument in the column L =SUM() formula refers to itself, and is therefore a circular reference, right? Should the formula be =SUM(B11,D11,F11,H11,K11)?John ... I float in liquid gardens
UTC 7ąDS

20090804, 11:18 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Column C contains circular references: for example the formula in L3 is =SUM(B3,D3,F3,H3,L3)  this formula refers to the cell that contains it. If you take L3 out of the formula and fill down, the "Calculate" sign will disappear.

20090810, 14:49 #4
 Join Date
 May 2002
 Posts
 413
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='787535' date='04Aug2009 17:18']Column C contains circular references: for example the formula in L3 is =SUM(B3,D3,F3,H3,L3)  this formula refers to the cell that contains it. If you take L3 out of the formula and fill down, the "Calculate" sign will disappear.[/quote]
er ah, uh duh! Thanks.
Why didn't the circular error message appear? I didn't see it, so I didn't consider it when reviewing the worksheet.

20090810, 15:02 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Perhaps you happened to press Enter precisely when the circular reference message appeared.
The "Calculate" indicator remaining permanently on in the status bar means that either automatic calculation has been turned off or that there's a circular reference. It wasn't the former, so it had to be the latter.

20090811, 11:22 #6
 Join Date
 May 2002
 Posts
 413
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='788318' date='10Aug2009 20:02']Perhaps you happened to press Enter precisely when the circular reference message appeared.
The "Calculate" indicator remaining permanently on in the status bar means that either automatic calculation has been turned off or that there's a circular reference. It wasn't the former, so it had to be the latter.[/quote]
thanks for the continuing education.
we've come a long way since visicalc!