Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Strange update (Excel 2000)

    I found a very strange thing.

    I have a list of 6 values with a formula of =Sum(a1:a3)-sum(a4:a6).All the value is nul.
    Now what is strange if I have a values in cell a1 to a3 and put a value in cell a4 the function changes to =Sum(a1:a4)-sum(a4:a6).

    Refer attached file

    Are there a place to put this nice/ irritating feature off.

    Thanks

    Mario
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    May 2002
    Location
    Midland, Michigan, USA
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange update (Excel 2000)

    Looking at your formulas, the formula for cell B7 is:
    <pre>=SUM(B1:B4)-(SUM(B4:B6)</pre>


    So, it's automatically subtracting the value of cell B4 from the equation as soon as it is added, yielding the same as the sum of B1:B3.
    (4+5+5+45)-(45) = (4+5+5)

    Perhaps you meant to use:
    <pre>=SUM(B1:B3)-(SUM(B4:B6)</pre>


    HTH

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange update (Excel 2000)

    I need a fix to this auto change Excel does.
    I have thought of either putting a hidden row in or making the formula constant
    Any other ideas?

    Thanks

    Mario

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Location
    Midland, Michigan, USA
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange update (Excel 2000)

    I'm a little bit confused - perhaps you can clarify.
    Which is the formula that you want to have in B7:
    =SUM(B1:B3)-SUM(B4:B6)
    OR
    =SUM(B1:B4)-SUM(B4:B6)
    And, given the data you supplied in your first post, what is the result that you expect?
    I don't quite understand the auto change that you're talking about <img src=/S/confused.gif border=0 alt=confused width=15 height=20>.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange update (Excel 2000)

    That does not seem to be happening for me. If I enter a value in cell A4 on your sheet, the formula does not change. Can you give a more detailed description of what you are doing?

    Also, check in Tools/Options on the Transition tab to make sure that "Transition navigation keys," "Transition formula evaluation," and "Transition formula entry" are not checked.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange update (Excel 2000)

    I have a list of values where I have 6 values underneath each other. The first 3 I would like to add and then deduct the sum of the last 3 values - formula Sum(A1:A3)-Sum(A4:A6). As soon as I enter value 1 to 3 and then enter a value in A4 the formula changes to Sum(A1:A4)-Sum(A4:A6). I've cleared the checks on "Transition formula evaluation," and "Transition formula entry" and saved the file.

    Now I would like Excel to keep the formula as Sum(A1:A3)-Sum(A4:A6) and not change it

    Thanks for all the input

    Regards

    Mario Smit

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange update (Excel 2000)

    Try:

    =SUM(OFFSET($A$1,0,0,3,1))-SUM(OFFSET($A$4,0,0,3,1))

    Aladin
    Microsoft MVP - Excel

  8. #8
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange update (Excel 2000)

    It's caused by "Extend list formats and formulas" on the Edit tab if this feature is checked.

    Aladin
    Microsoft MVP - Excel

Posting Permissions

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