Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Location
    Victoria, Br. Columbia, Canada
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Bug?? (2000 SR-1)

    Greetings,

    I've run into what looks like a very basic bug: is this a known problem?

    In a new workbook, type in the numbers 1,2 and 3 in cells A1:A3; then type in 4,5,and 6 in B1:B3, followed by 7,8 and 9 in D13 and 10,11,12 in E1:E3. Next, select cells A1:C3 and press the AutoSum button, and then select cells D1: F3 and press the AutoSum. So far, everything seems fine. Now, select A1:F4 and press AutoSum to get the column totals. The formula in cell F3 has changed! This makes no sense to me...

    TIA

    Mark

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    St. George, Maine, USA
    Posts
    158
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Bug?? (2000 SR-1)

    Mark,

    Interesting!
    More interesting is when the final AutoSum is changed from A1:F4 to c1:F4
    I have no immediate reason why this occurs, but one of the ways to avoid the issue is to simply select A1:G4 and then click AutoSum and then delete G1:G4.

    Good luck,
    Bob_D

  3. #3
    New Lounger
    Join Date
    Jan 2002
    Location
    Victoria, Br. Columbia, Canada
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Bug?? (2000 SR-1)

    Hi Bob_D,

    I haven't checked further into the conditions that trigger this odd behaviour. Workarounds are OK as long as one knows there's something that needs to be worked around! One doesn't expect existing formulae to be changed by these types of operations, so now the question is: under what conditions does this happen? This is really quite scary!

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Excel Bug?? (2000 SR-1)

    I can't reproduce the problem you see. - I'm using Excel 2000 without the service pack.
    The formula in F3 remains the same =sum(D3:E3).
    However, the formula's in c4 and f4 change from:
    c4 =SUM(A4:B4)
    f4 =SUM(D4:E4)
    to:
    c4 =SUM(C1:C3)
    f4 =SUM(F1:F3)
    However, this still gives the same result.
    Spreadsheets calculate from left to right, then from top to bottom. The change in formula reflects that order of calculation.

    Curious about your situation tho....
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    New Lounger
    Join Date
    Jan 2002
    Location
    Victoria, Br. Columbia, Canada
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Bug?? (2000 SR-1)

    I wonder if your results differ because the steps you took were a bit different than mine. My sequence of steps didn't have any formulae in row 4 until the last click on the AutoSum button. It appears that you had formulae in these cells prior to that step, as you noted their values changed from =SUM(A4:B4) to =SUM(C1:C3), etc.

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Excel Bug?? (2000 SR-1)

    for what it's worth, I was able to reproduce this in 2000 SR-1 doing exactly what you said.

    Fred

  7. #7
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Bug?? (2000 SR-1)

    Well, I also was able to replicate the bug. And I can't explain it (except for Catharine's explanation about calculation order).

    But my bigger question is, Why are you doing that?!
    I'm guessing that you've simplified a bug you found in a real spreadsheet. But bunching all those rows and columns of numbers together is not good spreadsheet form. Those different groups of numbers should be separated by empty rows and columns, and then your formulas would work as intended.

    BTW: I've removed the AutoSum button from my toolbar, as it's safer for me to type my own formula (I'm quite fast) and copy it across/ down, as needed. I also normally include a blank cell before and after the range in my Sum formula; that allows for much greater flexibility with the spreadsheet.

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Excel Bug?? (2000 SR-1)

    Richard,

    Don't know if your questions were aimed at me.

    I just played with Mark's "problem" to see if I could replicate it. I'm not building a spreadsheet as Mark suggested. I would probably do it more like you suggested.

    Fred

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Bug?? (2000 SR-1)

    You could say that there are two separate performing parts of the AutoSum function: <UL><LI>the "single" function - which deals with columns OR rows; and <LI>the "block" function - which deals with columns AND rows SIMULTANEOUSLY.[/list]In your first steps you are using the "single" function and in your final step you're using the "block" function. If you work through WebGenii's method, which uses the "block" feature throughout, there is no inconsistency. If, so to speak, you "change horses in midstream", you end up on a different horse.

    As you've probably realised, in order to achieve what you may have originally intended (an unchanged F3), you need to<UL><LI>AutoSum A1:E4 and then<LI>AutoSum F1:F4[/list]using the "single" function throughout.

    HTH
    Gre

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel Bug?? (2000 SR-1)

    The bug in question has carried on to excel 2002 (XP). However it realises that something is amiss as it reports an inconsistent formula in F3.

    Probably the best way to apply the AutoSum in that case is to select the cells A4:F4, and then apply the function.

    Andrew C

  11. #11
    New Lounger
    Join Date
    Jan 2002
    Location
    Victoria, Br. Columbia, Canada
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Bug?? (2000 SR-1)

    I may be missing something here, but I don't believe I'm mixing 'single' and 'block' functions: the first two uses of the Autosum button are each generating row totals (only), and the third application is generating column totals (only). No step is attempting to generate Autosum formulae for both rows and columns simultaneously.

    The fundamental issue that I see here is that (third) use of the Autosum button changed a pre-existing formula. This has got to be of concern to anyone who uses the Autosum button: if it can change a formula as per the example I provided, how would you ever know if it is or isn't changing formulae elsewhere?

  12. #12
    New Lounger
    Join Date
    Jan 2002
    Location
    Victoria, Br. Columbia, Canada
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Bug?? (2000 SR-1)

    Hi Richard,

    Yes, your supposition that I've simplified a real spreadsheet is correct.

    As far as good spreadsheet form, goes, I happened to be building a 'raw' data table, with no formatting. My report is then prepared on a separate tab, and references the data table via VLOOKUPs based on a nmumber of user-specified parameters. There shouldn't be any need to use blank separator columns in such a reference table.

    It's interesting that you've removed the AutoSum button from your toolbar: I may do the same now, given my lack of confidence in it's robustness!

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel Bug?? (2000 SR-1)

    I was able to reproduce the error another way in XL97.

    Assume the numbers are entered as you describe, then AutoSum A1:B3. NOW select A1:F4 and autosum. Same result, D3's formula gets goofy, only with one less step. A faster way to the same error always gives such a warm comfy feeling.

    I don't keep the AutoSum button because I use ALT-= to autosum, but it does exactly the same thing, and results in the same bug.
    -John ... I float in liquid gardens
    UTC -7DS

  14. #14
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Excel Bug?? (2000 SR-1)

    Whoops! Obviously I can't follow instructions ...
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Bug?? (2000 SR-1)

    Try clicking on A1:G4 and see what happens. There are no "hard numbers" in Column F, so the "block" function does not deliver any values to G1, G2 or G3. What is being read in the"block" A1:F4 is a rectangle with "hard numbers" in the corners A1, A3, E1 and E3. You are expecting the values in Column F to be treated as if they are "hard numbers". They are not.

    Generally, the only time I end up with values laid out with totals in both rows and columns is when I'm working with a Pivot Table. When it comes to AutoSum, I tend to stick to the "single" function, for caution, but my life's too short to do without it altogether! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Gre

Posting Permissions

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