Results 1 to 6 of 6

Thread: use of =sum

  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    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.
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    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='04-Aug-2009 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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='787535' date='04-Aug-2009 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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788318' date='10-Aug-2009 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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •